Grouping and Windowing - Part 2

Following on from Part 1, in this blog post we go deeper into Grouping and Windowing and talk about Multiple Grouping Sets

Multiple Grouping Sets

In summary, running this query

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY GROUPING SETS
(
( ID, Column1 ),
( Column1 ),
( )
)

produces this result

As you can see, the query adds the Cats,Dogs,Birds and Fish totals and produces a total at the end.

( ID, Column1 ), = Count indivisual rows
( Column1 ), = Sub Total
() = Grand Total

The query below uses the CUBE syntax

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY CUBE ( ID, Column1 )

and it produces this result.

 

As you can see, its does all the different combinations compared to the GROUPING SETS query.

Finally, there is the ROLLUP query

SELECT ID, Column1, COUNT(*) AS numpets
FROM dbo.Pets
GROUP BY ROLLUP ( ID, Column1 )

 

This produces the same results as the GROUPING SETS query but its done in a hierarchy layout.

That is about it for multiple grouping sets in SQL Server.