Using a Window Aggregate in an Aggregate Query

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

    😎

  • Thanks for the question. It was tricky for me.

  • 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

  • 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 26 (of 26 total)

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