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...
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.
1 Answer
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
