Using a Window Aggregate in an Aggregate Query

Question

Using a Window Aggregate in an Aggregate Query

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

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

Brian.Klinect Mr or Mrs. 500 Points: 592 More actions · Answer 1

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 More actions · Answer 2

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: 259115 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 11130 More actions · Answer 6

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: 104773 More actions · Answer 7

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 SSCoach Points: 17191 More actions · Answer 8

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

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 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

April 28, 2015 at 8:06 am

#1793944

Thanks for the question. It was tricky for me.

Damian Widera-396333 Ten Centuries Points: 1196 More actions · Answer 11

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: 2582 More actions · Answer 12

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.