Aggregating monthly sales data by column

  • 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

  • Hi Randy,

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

    Lars

  • 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'

  • 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.

  • 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

  • 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.

  • 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

  • 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.

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

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