SQL Server 2008 introduced GROUPING SETS as an extension to the GROUP BY, facilitating us to combine more than one group into a single result set. We can think it is as, combining multiple grouped aggregations into a single result set by using UNION ALL.
This is almost similar to CUBE and ROLLUP operators but easy to implement and offers best performance. Not only that, it is ANSI SQL 2006 compliant too.
Let’s understand this. The below code creates a SalesTransactions table and inserts 6 records. Then query to see the Sales Amount by (1.) Year, (2.) Customer, (3.) Year, Customer and Product. Result is shown too.
Okay, that’s the old technique, see how easily we can generate the same result set by using GROUPING SET. All you have to do is, add required grouping in GROUPING SET.
I see a small issue with this result set. Too much of NULLs. Do you see it too? These NULLs are not real NULLs, how can we identify whether the NULLs in the result set are real or not? There are two ways, you can use either GROUPING function that accepts one parameter as the column name or GROUPING_ID that accepts multiple columns as a parameter. First function returns 0 or 1 that indicates whether it is a real NULL or result of the GROUPING respectively. The second function returns a bitmask that shows whether passed columns contains NULLs or not.