Aggregate Queries

Question

Post reply

Aggregate Queries

Kathi Kellenberger

SSChampion

Points: 11811
More actions
November 29, 2005 at 8:28 pm

#166365

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/aggregatequeries.asp
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 14 posts - 1 through 13 (of 13 total)

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Mike Metcalf

SSCrazy Eights

Points: 8763

December 19, 2005 at 6:06 am

#610286

nice one aunt kathi

ivan gonzalez-195804 Old Hand Points: 304 More actions · Answer 2

Very nice, simple, and to the point. Appreciate it!!

Marie Wilkinson SSC Enthusiast Points: 178 More actions · Answer 3

It was helpful, thank you. I am wondering about your comments at the end of the article. I think the derived table is easier to understand but did you mean that or did you mean the derived table would be more efficient? Thanks.

Gladys Clemmer SSC Veteran Points: 280 More actions · Answer 4

Very helpful. I have struggled with aggregate queries in the past. Now I can just use your instructions!!

Thanks,

Gladys

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

Thanks, everyone.

From what I understand the derived table is more effiecient. It took me a while to understand them, though. The instructor I had when working on my degree taught us to do everything with sub-queries. The class was actually on PL SQL (Oracle's version) and some of the features are different.

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

Marie Wilkinson SSC Enthusiast Points: 178 More actions · Answer 6

Thanks so much! I like derived tables also so it's nice to know they are efficient also.

R Wong SSC Enthusiast Points: 122 More actions · Answer 7

Thank you! Easy to digest than BOL for a newbie like me.

Matthew Galbraith SSC Eights! Points: 995 More actions · Answer 8

Marie, in this case, I think the derived table just makes it more obvious what the query is doing than the "WHERE...IN" version. SQL should only have to actualize the recordset once either way though the join might be a little more effecient; I'd have to test a few cases to see if it mattered. Note that I generally avoid the IN and NOT IN constructs where possible since it usually results in the query optimizer generating a giant "OR" block to test each condition.

When you use a correlated subquery (essentially a sub query that uses fields from outside its parenthesis), SQL may execute the statement once per row that it needs to filter. In these cases, joining a derived table should almost always be more efficient, though this isn't the example in the article.

There's one other case that she didn't mention... What if you were doing a query for last order by Customer Name and your system doesn't have a unique key on name? I don't know of a way to do this without either setting up a temp table before running your aggregate query, or using a derived table. Maybe this only comes up when the schema is poorly defined, but I have found it relevant when querying third-party databases. (Specific case in point: we have such a database that allows SSN's to be duplicated in an employee table; when looking up aggregate data per employee, I have to get the most recent employee id number for each SSN and use it when agregating data for each employee.)

Matthew Galbraith

Marie Wilkinson SSC Enthusiast Points: 178 More actions · Answer 9

Thank you Matthew, your comments about correlated subqueries were also helpful, I had added just that type of subquery to a procedure and watched it almost grind to a halt. Went back and made it a derived table and it's flying again.

Marie

Bill Scott SSCrazy Points: 2338 More actions · Answer 10

Thanks for the best description of what the HAVING clause is for that i have ever seen!

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 11

Cool! Glad it helped!

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

Joseph D'Elia Grasshopper Points: 17 More actions · Answer 12

Thanks for an excellent article.

Another article on with rollup and with cube would be helpful also.

josephd

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 13

Here's another vote for an article on ALL, WITH ROLLUP and WITH CUBE. Thanks for the excellent explanation.

Steve Eckhart