Creating Subtotals and Totals in Aggregated Queries

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4795

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

  • David.Poole

    SSC Guru

    Points: 75108

    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?

  • Dennis Wagner-347763

    SSC Eights!

    Points: 945

    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: 76491

    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: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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