Using OVER with an Aggregate Function

Question

Post reply

Using OVER with an Aggregate Function

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
November 3, 2010 at 8:12 pm

#242611

Comments posted to this topic are about the item Using OVER with an Aggregate Function
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

Viewing 15 posts - 1 through 15 (of 32 total)

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

mislamuddin Grasshopper Points: 11 More actions · Answer 1

Hi:

If select distinct specified order by item must appear in the select list and there will be error message as follows:

Msg 145, Line 15 St 1

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 2

Iulian -207023

SSCertifiable

Points: 7639

November 4, 2010 at 2:13 am

#1246403

Nice question, thank you.

It works for me.

Iulian

Imran Ashraf-452633 SSCommitted Points: 1969 More actions · Answer 3

Imran Ashraf-452633

SSCommitted

Points: 1969

November 4, 2010 at 2:16 am

#1246404

Good question learned something new today.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

mislamuddin (11/3/2010)
If select distinct specified order by item must appear in the select list

It does. The code window has a scroll bar at the bottom; you'll find the column sstr.StateProvinceID to the far right.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Imran Ashraf-452633 (11/4/2010)
Good question learned something new today.

(Not targeting you specifically, but everyone who posted and/or will post similar comments)

The question is good indeed - but please don't learn too much from this.

If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKSUM, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

SELECT MAX(TaxRate), StateProvinceID

FROM Sales.SalesTaxRate

GROUP BY StateProvinceID

ORDER BY StateProvinceID;

Much easier to understand and maintain in the future, and a more efficient execution plan to boot!

(EDIT: Fixed a silly typo - thanks, Oleg, for the heads-up!)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Toreador SSChampion Points: 11365 More actions · Answer 6

I knew what the answer was, and thought that "The maximum Tax Rates across all State/Province IDs" described it well. I supopose I should have read more closely and realised that "The maximum Tax Rates per State/Province ID" also described it well, then tried to work out the difference between the two statements. Though I'd probably have picked the wrong one anyway.

Should it have said "The maximum Tax Rate [singular] across all State/Province IDs"?

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 7

Goo question, thanks, but I don't understand why this was a two point question...

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

I have never used the OVER function before. I had to do a bit of reading to understand the question. I learned about a useful, but limited, function. Good question, thanks.

brdudley SSCrazy Points: 2320 More actions · Answer 9

The existence of the Distinct in the query confused me, at first.

The key to using the Over as opposed to doing a Group By is really when you want to see all the rows in the group, but include an aggregate value.

With this example, where the only other field being displayed is the StateProvinceID, multiple rows for the same State add little value -- this is likely where the Distinct comes in.

I have used the Over clause for Row_Number, but not for aggregates, so this question gave me a few things to explore.

brdudley SSCrazy Points: 2320 More actions · Answer 10

Hugo Kornelis (11/4/2010)
(If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

Select StateProvinceID,

TaxType,

Max(TaxRate) Over (Partition by StateProvinceID),

Max(TaxRate) Over (Partition by StateProvinceID, TaxType)

from Sales.SalesTaxRate

Order by StateProvinceID

SanDroid SSChampion Points: 10068 More actions · Answer 11

mislamuddin (11/3/2010)
Hi:
If select distinct specified order by item must appear in the select list and there will be error message as follows:
Msg 145, Line 15 St 1

I did not get this error.

Of course I also did not get the differance between the first two answers. 😎

Nice question about a great feature!

sjimmo SSChampion Points: 11139 More actions · Answer 12

Very good question - I learned something today.

Hugo
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

To look at the question and only talk about it's limitations makes one wonder why is it there.

brdudley
One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

Excellent addition to the questions functionality. I learned something more, and have already started playing with tests to see about changes, especially in some complex reports.

Thanks

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the question. I had never considered using the OVER in this way.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cdesmarais 49673 SSCommitted Points: 1863 More actions · Answer 14

I worry that the DISTINCT is going to give people the impression that OVER/PARTITION does the same thing as GROUP BY. It's an important distinction and the explanation doesn't make it clear.