Using PIVOT to aggregate monthly counts of products purchased and revenue generated

Question

I've searched this site and have done Google searches for how to pivot multiple columns (sales counts and revenue by month), but haven't quite found what I'm looking for. I admit that I haven't completely wrapped my head around PIVOT-ing yet, so it's possible I just wasn't fully understanding a solution that has already been posted.

What I'm trying to do is construct a query using PIVOT that will show monthly counts, as well as monthly revenue for products purchased.

Here is what the data looks like:

Product | MonthPurchased | InvoiceNmber | PaymentAmount
====================================================
Pencil          1             10001           1.00 
Pencil          2             10005           1.00 
Pen             1             10002           2.00  
Paper           2             10006           1.00  
Pen             1             10003           2.00
Paper           1             10004           1.00

Here is the query I've put together:

INSERT INTO #temp_nbcc_products
        ( [Product], [Month], t_invoice_num, t_payment_amount )

SELECT prd_name, DATEPART(mm, pyd_add_date), invoice_num, payment_amount
FROM product_table ...

SELECT [Product]
    , [1] AS Jan
    , [2] AS Feb
    , [3] AS Mar
    , [4] AS Apr
    , [5] AS May
    , [6] AS June
    , [7] AS July
    , [8] AS Aug
    , [9] AS Sept
    , [10] AS Oct
    , [11] AS Nov
    , [12] AS Dec
    , [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] AS [Total Count]
FROM
    (
    SELECT t_payment_amount, t_invoice_num, [Month], [Product]
    FROM #temp_nbcc_products
    ) src

PIVOT

    (
     COUNT(t_invoice_num)
     FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) piv1

ORDER BY [Product] DESC;

Here is the output of the query:

Product | Jan | Feb | March .... Total Counts
=============================================
Pencil     1     1      0             2
Pen        2     0      0             2
Paper      1     1      0             2

What I'd like the result set to look like is this:

Product | Jan | Revenue-Jan | Feb | Revenue-Feb .... TotalCounts | TotalRev
========================================================================
Pencil     1       1.00        1       1.00               2          2.00
Pen        2       4.00        0       0.00               2          4.00
Paper      1       1.00        1       1.00               2          2.00

Any help would be greatly appreciated.

score 1 · Accepted Answer · answered Jan 16 '18 at 20:44

I think conditional aggregation is easier:

SELECT product,
       sum(case when [Month] = 1 then 1 else 0 end) as jan,
       sum(case when [Month] = 1 then t_payment_amount else 0 end) as jan_revenue,
       sum(case when [Month] = 2 then 1 else 0 end) as feb,
       sum(case when [Month] = 2 then t_payment_amount else 0 end) as feb_revenue,
       . . .
FROM #temp_nbcc_products
GROUP BY product

score 0 · Answer 2 · answered Jan 16 '18 at 20:49

Looks like you know how to use PIVOT.

Using GROUP BY Just make your base dataset before the PIVOT looks like:

Product | MonthPurchased | TotalMonthCount| TotalPaymentAmount
====================================================
Pencil          1             2              1.00 
Pencil          2             2              1.00

Then you can do the PIVOT for multiple columns.

In Sql Server how to Pivot for multiple columns

Using PIVOT to aggregate monthly counts of products purchased and revenue generated

2 Answers2