Kevin Wilkie has fun with grouping sets:
Let’s look at our dbo.Person1 table that we worked with earlier. Today, I want to find a count of all of the persons in each of the following categories: ZipCode, Gender, and Email Domains. And just for fun, let’s add in there where each of those categories cross – for example, Zipcode and Gender, ZipCode and Email Domain, etc…
Most people would think all kinds of awful thoughts at this point about all of the GROUP BY statements you’ll have to write. For anyone wondering – this is one way to do it. Notice all kinds of UNION statements and I’m sure someone is wondering if that’s truly all of the combinations. And we don’t want to go into the maintenance on this if things do happen to change…
And don’t forget about the GROUPING() function:
Let’s say our business partner asks us to determine which fields are aggregated together. Since we only have 2 fields and a grand total of 15 rows, we could determine this by eye. But, like all good developers, we want to do this programmatically.
Here’s where our friend – the GROUPING() function – comes into play.
GROUPING SETS is an extremely useful operator in the ANSI SQL standard. Definitely worth learning how to use.