Creating Subtotals and Totals in Aggregated Queries

Question

Creating Subtotals and Totals in Aggregated Queries

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
June 30, 2018 at 4:20 am

#356631

Comments posted to this topic are about the item Creating Subtotals and Totals in Aggregated Queries

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76000 More actions · Answer 1

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?

LinkedIn Profile

Dennis Wagner-347763 SSC Eights! Points: 945 More actions · Answer 2

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

drew.allen SSC Guru Points: 76985 More actions · Answer 3

David.Poole - Thursday, September 6, 2018 1:16 AM
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?

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.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

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
GROUPING() function
GROUPING_ID() function

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)