Aggregating monthly sales data by column

Question

Post reply

Aggregating monthly sales data by column

rrdavis07

SSC Veteran

Points: 213
More actions
November 30, 2011 at 11:15 pm

#247009

I've been given a task to create a query to return 3 years of sales data in monthly aggregate format.
Here is the data I start with:
OrderID
ItemCode
OrderDate
OrderQty
This is transactional data, so there will be several dozen orders in a single day. I want to end with the following columns:
ItemCode | Qty Shipped in Month1 | Qty Shipped in Month2 | Qty Shipped in Month3 | etc |
I've struggled with this for a couple of hours now, and have ended up with something like this
select Distinct
ItemCode
, (select sum(orderQty) from table1 where YEAR(ShipDate) = '2010' and MONTH(ShipDate) = '1')
, (select sum(orderQty) from table1 where YEAR(ShipDate) = '2010' and MONTH(ShipDate) = '2')
, (select sum(orderQty) from table1 where YEAR(ShipDate) = '2010' and MONTH(ShipDate) = '3')
...
from table1
There is obviously more to the code than this, but my question is: Is there any easier way to write this with other than 36 nested select statements?
Thank you for your talents.
Randy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

larsjessenhh SSC Veteran Points: 280 More actions · Answer 1

Hi Randy,

you should have a look at the PIVOT function, in BOL it's documented somewhere under the FROM... clause.

Lars

harsh.lapashiya Old Hand Points: 398 More actions · Answer 2

select SUM(OrderQty) 'Quantity', convert(varchar,month(OrderDate))+'-'+convert(varchar,YEAR(OrderDate)) as 'Month year'

from table1

group by MONTH(OrderDate),YEAR(OrderDate)

having year(OrderDate) between '2008' and '2011'

Usman Butt Hall of Fame Points: 3186 More actions · Answer 3

There is no need to scan the whole table again and again. You should use CASE to sort this out. Its pretty much simple e.g.

SELECT

ItemCode

, SUM( CASE WHEN YEAR(ShipDate) = '2010' AND MONTH(ShipDate) = '1' THEN orderQty ELSE 0 END) [Qty Shipped in Month1]

, SUM( CASE WHEN YEAR(ShipDate) = '2010' AND MONTH(ShipDate) = '2' THEN orderQty ELSE 0 END) [Qty Shipped in Month2]

, SUM( CASE WHEN YEAR(ShipDate) = '2010' AND MONTH(ShipDate) = '3' THEN orderQty ELSE 0 END) [Qty Shipped in Month3]

....

from table1

GROUP BY ItemCode

Cheers.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:

SELECT

ItemCode,

Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 1 THEN orderQty END),

Column2 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 2 THEN orderQty END),

Column3 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 3 THEN orderQty END)

FROM (

SELECT

ItemCode,

YearShipped= YEAR(ShipDate),

MonthShipped= MONTH(ShipDate),

orderQty= SUM(orderQty)

FROM table1

GROUP BY ItemCode, YEAR(ShipDate), MONTH(ShipDate)

) PreAgg

GROUP BY ItemCode

ORDER BY ItemCode

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Usman Butt Hall of Fame Points: 3186 More actions · Answer 5

ChrisM@Work (12/1/2011)
Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:
SELECT
ItemCode,
Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 1 THEN orderQty END),
Column2 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 2 THEN orderQty END),
Column3 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 3 THEN orderQty END)
FROM (
SELECT
ItemCode,
YearShipped= YEAR(ShipDate),
MonthShipped= MONTH(ShipDate),
orderQty= SUM(orderQty)
FROM table1
GROUP BY ItemCode, YEAR(ShipDate), MONTH(ShipDate)
) PreAgg
GROUP BY ItemCode
ORDER BY ItemCode

It seems to me doing a bit more work than my solution. Sorry, but Do not have the time to test the performance. Please update if you can do it. Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Usman Butt (12/1/2011)
ChrisM@Work (12/1/2011)
Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:
SELECT
ItemCode,
Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 1 THEN orderQty END),
Column2 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 2 THEN orderQty END),
Column3 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 3 THEN orderQty END)
FROM (
SELECT
ItemCode,
YearShipped= YEAR(ShipDate),
MonthShipped= MONTH(ShipDate),
orderQty= SUM(orderQty)
FROM table1
GROUP BY ItemCode, YEAR(ShipDate), MONTH(ShipDate)
) PreAgg
GROUP BY ItemCode
ORDER BY ItemCode
It seems to me doing a bit more work than my solution. Sorry, but Do not have the time to test the performance. Please update if you can do it. Thanks.

No need, it's covered in Jeff Moden's excellent article Cross Tabs and Pivots, Part I[/url]. Try to find the time to read it 😉

Preaggregating data for pivots or crosstabs often offers a very significant performance boost.

I always try with and without preaggregation because the performance gain is proportional to the granularity of the aggregation, and in any case it only takes minutes to do.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Usman Butt Hall of Fame Points: 3186 More actions · Answer 7

ChrisM@Work (12/1/2011)
Usman Butt (12/1/2011)
ChrisM@Work (12/1/2011)
Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:
SELECT
ItemCode,
Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 1 THEN orderQty END),
Column2 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 2 THEN orderQty END),
Column3 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped = 3 THEN orderQty END)
FROM (
SELECT
ItemCode,
YearShipped= YEAR(ShipDate),
MonthShipped= MONTH(ShipDate),
orderQty= SUM(orderQty)
FROM table1
GROUP BY ItemCode, YEAR(ShipDate), MONTH(ShipDate)
) PreAgg
GROUP BY ItemCode
ORDER BY ItemCode
It seems to me doing a bit more work than my solution. Sorry, but Do not have the time to test the performance. Please update if you can do it. Thanks.
No need, it's covered in Jeff Moden's excellent article Cross Tabs and Pivots, Part I[/url]. Try to find the time to read it 😉
Preaggregating data for pivots or crosstabs often offers a very significant performance boost.
I always try with and without preaggregation because the performance gain is proportional to the granularity of the aggregation, and in any case it only takes minutes to do.

Ahh, my mistake. You are right. How can I forget that. Thanks for reminding me.