The GROUP BY clause has some powerful capabilities. Most of us know that it allows and performs aggregate functions for each unique group returned from the GROUP BY definition. You can do things like Average, Min, Max, Count, or even write your own CLR aggregate function. I like to use my own Median CRS aggregate function for example.
However, the GROUP BY clause has even more capability, much of it specified in 2006 ANSI SQL specification. In addition to a simple GROUP BY clause, you can also use GROUP BY SETs, GROUP BY ROLLUP and GROUP BY CUBE.
GROUP BY SETS allow you to combine the results of multiple GROUP BY clauses into an aggregate GROUP BY producing a union of the results.
GROUP BY ROLLUP returns all the unique values of the columns listed in the Group By. Additionally it includes unique groups by replacing the values returned with null, starting with the right most column in the group by clause, until all columns are null except the first column. Here’s an example with three columns in the GROUP BY ROLLLUP clause:
Col1, Col2 Col3
Col1, Col2, null
Col1, null, null
GROUP BY CUBE differs slightly by returning every possible permutation, including null for all of the columns in the GROUP BY clause. Following is an example with just two columns:
Col1, Col2
Col1, null
null, Col2
null, null
For more information on the GROUP BY clause there’s some good documentation to be found at https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql. It explains even more power ways to utilize the GROUP BY clause, and how you can use query hints to improve performance in different environments
.Cheers,
Ben