Using a Window Aggregate in an Aggregate Query

  • Brian.Klinect

    Mr or Mrs. 500

    Points: 592

    The explanation says why 3 is wrong, but why is 1 better than 2? The only difference I see is 1 has SUM(SUM(TotalDue)), while 2 has SUM(TotalDue) in the dividend of the percentage.

    Why is the additional SUM necessary?

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    palotaiarpad (4/26/2015)


    Hmm. According to BOL, if OVER used with a SUM, then an order by clause is required...

    Be sure to reread the question and try all three queries in the answers before you say this. When you add the ORDER BY clause, you are calculating a running total. That is different.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Brian.Klinect (4/27/2015)


    The explanation says why 3 is wrong, but why is 1 better than 2? The only difference I see is 1 has SUM(SUM(TotalDue)), while 2 has SUM(TotalDue) in the dividend of the percentage.

    Why is the additional SUM necessary?

    Because you are calculating the SUM using a window function over SUM(TotalDue). This last one is calculated by the GROUP BY.

    So you have two different kinds of SUM in one query.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    palotaiarpad (4/27/2015)


    Link is already in my first post, but please: https://msdn.microsoft.com/en-us/library/ms187810.aspx

    I would say BOL does have an error. Not the first time. You can definitely use the OVER clause with SUM without an ORDER BY n the OVER. When you add the ORDER BY it is something different, a running or moving aggregate.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    Brian.Klinect (4/27/2015)


    The explanation says why 3 is wrong, but why is 1 better than 2? The only difference I see is 1 has SUM(SUM(TotalDue)), while 2 has SUM(TotalDue) in the dividend of the percentage.

    Why is the additional SUM necessary?

    Try running #2. You will get an error. The window function must apply to an aggregated column or a column in the group by. Here is my blog post about this: http://auntkathisql.com/2014/09/23/how-to-use-a-window-aggregate-in-an-aggregate-query/

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Rich Mechaber

    SSChampion

    Points: 10935

    Got it right.

    Feel like a chump.

    Been writing queries like this using a CTE to return line-item subtotals, then a final query to return those plus the per-row, fraction-of-the-grand-total percentages.

    Kathi, you just saved me a step in a number of procs -- that's a very cool construct. Thank you! I'll be running some query plans and stats to check, but I have to imagine your approach is at least as efficient (and probably more) than mine.

    Rich

  • TomThomson

    SSC Guru

    Points: 104772

    Very nice question.

    It's interestng that 12% or answers to date appear to come from people who beleive that allorders in any given year must occur on teh same date. :hehe:

    Tom

  • Ken Wymore

    SSCoach

    Points: 16523

    Nice question Kathi. This is a much simpler method to do this than what I have used before.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    TomThomson (4/27/2015)


    Very nice question.

    It's interestng that 12% or answers to date appear to come from people who beleive that allorders in any given year must occur on teh same date. :hehe:

    Relics from the not so far past when offices took orders on certain days:-D

    😎

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question. It was tricky for me.

  • Damian Widera-396333

    Ten Centuries

    Points: 1186

    Trying to test that and see strange error 🙂

    Msg 6517, Level 16, State 1, Line 2

    Failed to create AppDomain "master.sys[runtime].37".

    Exception has been thrown by the target of an invocation.

    Kindest Regards,

    Damian Widera
    SQL Server MVP,
    MCT, MCSE Data Platform, MCSD.NET

  • stephen.long.1

    SSCrazy

    Points: 2577

    Thanks for the terrific question, Kathi! The only criticism I have is that your explanation only states why number 3 is wrong and does not discuss the difference between number 1 and number 2.

    I was able to eliminate number 3 right away, because it would give you one result for each date, not for each year, but then I thought "Why in the world would you do SUM(SUM(TotalDue))? There must be something tricky about using an empty OVER() clause." After investigating that, I finally realized that SUM(TotalDue) / SUM(TotalDue) would just give you 1 for each row (assuming TotalDue is not 0 for a given year), so that isn't correct, and then I re-read the question and realized you were looking for the percentage of the grand total, in which case the first query was correct (the yearly total / the grand total).

    Once again, thank you for making me think.

Viewing 12 posts - 16 through 27 (of 27 total)

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