SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Derived Tables to Calculate Aggregate Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/18/2001 :  21:42:35  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Calculating aggregate values can be simplified by using derived tables. In this article I show you how to use derived tables to calculate two independent aggregate values in a single SELECT statement.

Article Link.

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/19/2001 :  14:54:43  Show Profile  Reply with Quote
Congratulations for noting that derived tables need to be named: I'm sure I'm not alone in banging my head, initially trying to get them to work without.

My favorite simple use for derived tables is aggregating aggregates:
SELECT ReferralCount, COUNT(*) AS PatientCount
FROM (SELECT COUNT(*) AS ReferralCount FROM Referrals GROUP BY PatID) AS a
GROUP BY ReferralCount
ORDER BY ReferralCount

which is so much nicer having to create supplementary views / temporary tables.

A few things struck me: you say you want to "calculate current month [...] sales", but the query attempts to use the previous month. Unfortunately, it does so in a way that a @RunDate value in January fails to work. Actually, since the two derived tables are INNER JOINed, if there were no sales in the previous month, there will be no rows in the result.

I'm also slightly concerned that you've split the query into two subqueries, but at least the month has a non-sargable condition. In this case, you don't benefit from doing the two parts separately, and there's no need to since the GROUP BY's are both on Sto_Name.
SELECT b.Sto_Name,
COUNT(CASE WHEN DATEDIFF(mm, Sal_Date, @RunDate) = 1
THEN 1 ELSE NULL END) AS CM_Count,
SUM(CASE WHEN DATEDIFF(mm, Sal_Date, @RunDate) = 1
THEN Sal_Amt ELSE NULL END) AS CM_Sales,
COUNT(CASE WHEN DATEPART(yy, Sal_Date) = DATEPART(yy, @RunDate)
THEN 1 ELSE NULL END) AS YTD_Count,
SUM(CASE WHEN DATEPART(yy, Sal_Date) = DATEPART(yy, @RunDate)
THEN Sal_Amt ELSE NULL END) AS YTD_Sales
FROM Sales a JOIN Stores b ON a.Sto_ID = b.Sto_ID
GROUP BY b.Sto_Name

Course, this might be a bad idea if the WHERE clauses had used BETWEEN.

I also don't like US date-format, but that's just me.

Edited by - Arnold Fribble on 11/20/2001 04:24:10
Go to Top of Page

sqlweenie
Starting Member

1 Posts

Posted - 03/22/2002 :  10:31:30  Show Profile  Reply with Quote
First of all, I love this site -- or else I wouldn't have just signed up. I'm primarily a web developer, but I'm starting to try and focus on the SQL track now, and this site is an invaluable resource for researching some of the many, MANY questions that enter my head these days.

That being said -- excellent article for us n00bs! I've always had a hard time seeing how to blend aggregate and standard reporting data (e.g. company name, location, etc). But you've given both an example and a name, and a very simple yet relevant one at that, and that's exactly what I needed. Again, thank you.

Go to Top of Page

manieverster
Starting Member

South Africa
5 Posts

Posted - 04/18/2009 :  04:26:53  Show Profile  Reply with Quote
I like your article. It explained the use of derived tables very nicely to me. I also just registered on this site and hope I will get much more and also give alot of what I know.

Manie Verster
Developer - SQL and ASP.Net
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000