Comments posted to this topic are about the item Creating Subtotals and Totals in Aggregated Queries
I'm not clear what this syntax adds over the old WITH ROLLUP and WITH CUBE? Is it a more ANSI compatible version of the same thing?
A great way to "clean up" the query is the use of the GROUPING() function to remove the NULLs. It can also be used to have better control on ordering your results either with the sub totals first (DESC) or last (ASC).
SELECT CASE GROUPING(MakeName) WHEN 1 THEN 'Total' ELSE MakeName END AS MakeName,
CASE GROUPING(Color) WHEN 1 THEN 'Total' ELSE Color END AS Color,
SUM(Cost) AS Cost
FROM Data.Make MK
INNER JOIN Data.Model MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock ST ON ST.ModelID = MD.ModelID
GROUP BY GROUPING SETS ((MakeName, Color), (MakeName), (Color), ())
ORDER BY GROUPING(MakeName), MakeName, GROUPING(Color), Color
Also if you wanted to include the MakeID in the query results but you didn't want it to add an extra layer of subtotals, you can include a HAVING at the end to "pair" MakeID with MakeName
HAVING GROUPING(MK.MakeID) = GROUPING(MK.MakeName)
so instead of having individual subtotals for each, they will always be paired together
David.Poole - Thursday, September 6, 2018 1:16 AM
It gives you much more flexibility in which totals you want to output. I don't have a good example off the top of my head.
J. Drew Allen
Business Intelligence Analyst
You shouldn't rely on NULLs for the detection of totals and subtotals because some of the groups can be based on an aggregate of things that have NULL "labels". You can also control the sorted output of an ORDER BY and you can surgically change the NULLs to an appropriate title. You just need to learn how to using the GROUPING() function.
It's a bit of a shame that the GROUPING() function wasn't even mentioned in this article because it provides some awesome functionality. The related GROUPING_ID() function is very useful, as well, although the MS documentation on both functions is a bit overly complicated for newbies to the subject.
GROUP BY syntax
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)