Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Aggregate Queries


Aggregate Queries

Author
Message
Kathi Kellenberger
Kathi Kellenberger
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 341
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/aggregatequeries.asp

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Mike Metcalf
Mike  Metcalf
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 53
nice one aunt kathi
ivan gonzalez-195804
ivan gonzalez-195804
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 1
Very nice, simple, and to the point. Appreciate it!!
Marie Wilkinson
Marie Wilkinson
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 129
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
Gladys Clemmer
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1

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

Thanks,

Gladys


Kathi Kellenberger
Kathi Kellenberger
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 341

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Marie Wilkinson
Marie Wilkinson
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 129
Thanks so much! I like derived tables also so it's nice to know they are efficient also.
R Wong
R Wong
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 49
Thank you! Easy to digest than BOL for a newbie like me.
Matthew Galbraith
Matthew Galbraith
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 4
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
Marie Wilkinson
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 129

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search