GROUPING SETS – SQL Server 2008 (Video Tutorial)

Leave a comment (1) Go to comments

You can use the GROUPING SETS extension of the GROUP BY clause to specify multiple groupings of data in one query. It generates the same result set that you would obtain by using the UNION ALL clause, but in a more efficient way. You can use GROUPING SETS to simplify complex aggregation queries into one query.

Grouping Sets Video Demo

For example, the two queries that are shown in the following code example both return the same result sets.

– Using UNION ALL

SELECT CustomerID, NULL AS Year, SUM(SubTotal)


FROM Sales.SalesOrderHeader

GROUP BY CustomerID

UNION ALL

SELECT NULL AS CustomerID, YEAR(OrderDate), SUM(SubTotal)

FROM Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)


– Using GROUPING SETS

SELECT CustomerID, YEAR(OrderDate), (SubTotal)

FROM Sales.SalesOrderHeader

GROUP BY GROUPING SETS ((CustomerID), (YEAR(OrderDate)))



The GROUP BY clause also now supports the ROLLUP and CUBE extensions, which replace the WITH ROLLUP and WITH CUBE extensions from SQL Server 2005.

EOF - GROUPING SETS – SQL Server 2008 (Video Tutorial), SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: