# 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

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

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)