select multiple aggregates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select multiple aggregates

Author

Topic

caisys
Starting Member

16 Posts

Posted - 2004-01-16 : 13:35:18

I want to create a query to summarize sales for each of the last five weeks and group by product type ie.

select product, sum(amount this week), sum of (amount last week) ... group by product

my sales table looks like this

product date amount
x d1 xx
x d2 xx
y d3 xx
y d4 xx
x d5 xx

How can this be done?

thanks

nricardo
Starting Member

17 Posts

Posted - 2004-01-16 : 13:51:20

select product,
sum(case when datediff(week, date, getdate()) = 0 then amount else $0.00 end) as "this_week",
sum(case when datediff(week, date, getdate()) = 1 then amount else $0.00 end) as "last_week",
...
group by product

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 13:56:02

Here's my crack at it:



SELECT DISTINCT s1.product, week1, week2
FROM sales s1
INNER JOIN (
	SELECT product, SUM(amount) AS week1
	FROM sales
	WHERE [date] BETWEEN GETDATE() - 7 and GETDATE()
	GROUP BY product) s2
ON s1.product = s2.product
INNER JOIN (
	SELECT product, SUM(amount) AS week2
	FROM sales
	WHERE [date] BETWEEN GETDATE() - 14 and GETDATE() - 7
	GROUP BY product) s3
ON s1.product = s3.product
...

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources