December 15, 2009 at 10:11 am
I'd like to solicit input from the memebrs on what they feel is the BEST PRACTICES approach to handling the scenario where you have a query that must show Aggregate values along with the Detail level info that is being aggregated.
For example if I wanted to write a query that would show all the States in the US along with a count of how many counties is in each state and the name of each County I could not use the GROUP BY because the inclusion of the County names would prevent a correct Count of the counties. The most common approach I've seen to do this is to use sub-querys like this:
SELECT S.Name AS 'StateName',
(SELECT Count(C.sName) FROM COUNTY C WHERE S.ID = C.iState ) AS 'NumCounties'
C.sName AS 'CountyName'
FROM STATE S Left Outer Join COUNTY C ON S.ID = C.iState
WHERE 1 = 1
The above is an example I made up but it clearly shows what I'm describing.
Another example in which the sub-query is used is where the Aggregated values come from different tables. Lets say I wanted to expand the above so that In addition to the count of Counties in each state I got a count of zip codes in each County and each state. Here I would use another sub-query.
I'm not looking for ho wbest to use a sub-query to do this but if there is an alternate method and if not then what would most of you consider to be a Best Practices approach to this using sub-queries.
I know the above is using theoretical tables and not my real data but thats because we have an NDA and can not provide actual; Table structures and so I selected the States - COunties - Zip codes as an example since its simple and a data set everyone would be familiar with.
Thanks
Kindest Regards,
Just say No to Facebook!December 16, 2009 at 7:35 am
Sometimes you get better performance by doing the aggregating in a CTE or a derived table. Something like:
SELECT
S.Name AS StateName,
C.sName AS CountyName,
County_Count.counties
FROM
STATE S JOIN
County C ON
S.id = C.iState JOIN
(
SELECT
C.iState,
COUNT(cName) AS counties
FROM
County AS C
GROUP BY
C.istate
) AS County_Count ON
S.id = C.istate
WHERE 1 = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 16, 2009 at 7:42 am
I'd go along with Jack on this one.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply