CODEWITHSUNDEEP
sqlsql-server
user2943775
user2943775

Reputation: 273

SQL query group by multiple time periods

I have the following table:

SalesDate smalldatetime,
ProductId varchar(20),
QuantitySold tinyint,
Price smallmoney

I want to get the last 1m, 3m, 1y total quantity sold for each product. How do I do that in one query? My attempt so far is to run three separate queries, one for each time period, and then combine them using UNION. Is there a better way? I am using MS SQL 2008.

Upvotes: 2

Views: 3605

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can more easily do this with multiple columns:

select productId,
       sum(case when salesdate >= dateadd(month, -1, getdate()) then quantitysold else 0 end) as qs1m,
       sum(case when salesdate >= dateadd(month, -3, getdate()) then quantitysold else 0 end) as qs3m
       sum(case when salesdate >= dateadd(month, -12, getdate()) then quantitysold else 0 end) as qs12m
from table t
group by productId;

(Note: You probably want to at least remove the times from getdate(), but you might have an entirely different way of getting the "as-of" date.)

Because the time periods overlap, it is easier to do this with columns (and conditional aggregation) than with a separate row for each time period.

Upvotes: 6

Related Questions