Produce a per customer per month sales report via SQL eg group by two columns and sum the third

Question

I rarely write SQL (Azure SQL) however I am trying to generate a per month sales total per customer.

Customer:

    |Username     |ID    |
    |user1        |1     |
    |user2        |2     |
    |user3        |3     |

Order:

    |CustomerId   |Month |Total     |
    |1            |1     |275       |
    |1            |1     |10        |
    |2            |1     |100       |
    |1            |3     |150       |
    |2            |2     |150       |
    |2            |2     |65        |
    |3            |2     |150       |

I want to produce

|Username     |Month1Total |Month2Total | Month3Total |
|user1        |285         |275         |     150     |
|user2        |100         |215         |      0      |
|user3        |0           |150         |      0      |

I can do the following

SELECT customerTable.Username Username, SUM(orderTable.OrderTotal) TotalMay
FROM "Order" orderTable
JOIN Customer customerTable ON orderTable.CustomerId = customerTable.Id
WHERE DATENAME(Month, (orderTable.PaidDateUTC)) = 'May'
GROUP BY Username

Which will give me an output per month. However I don't know how to loop this, do it per month and then group by username.

Hi. This is a faq. Please always google many clear, concise & specificversions/phrasings of your question/problem/goal with & without your particularstrings/names & read many answers. Add relevant keywords you discover to yoursearches. If you don't find an answer then post, using 1 variant search as title &keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Oct 03 '18 at 07:50

score 1 · Accepted Answer · answered Oct 03 '18 at 07:41

IF you want to have a separate column for each month then try this

SELECT customerTable.Username Username
,SUM(iif(ordertable.[month] = 1,orderTable.OrderTotal,0)) TotalJan
,SUM(iif(ordertable.[month] = 2,orderTable.OrderTotal,0)) TotalFeb
,SUM(iif(ordertable.[month] = 3,orderTable.OrderTotal,0)) TotalMar
,SUM(iif(ordertable.[month] = 4,orderTable.OrderTotal,0)) TotalApr
,SUM(iif(ordertable.[month] = 5,orderTable.OrderTotal,0)) TotalMay

FROM "Order" orderTable
JOIN Customer customerTable ON orderTable.CustomerId = customerTable.Id

GROUP BY Username

should be easy to add the remaining months

Zaynul Abadin Tuhin · Answer 2 · 2018-10-03T08:39:03.470

1

you can use case when

  with t1 as
   ( select o.CustomerId,o.Month, sum(Total) as total from
      [Order] 
     group by o.CustomerId,o.Month
  ) select c.Username, 
   case when t1.month=1 then t1.total else 0 end month1,
   case when t1.month=2 then t1.total else 0 end month2,
   case when t1.month=3 then t1.total else 0 end month3 
   from t1 join Customer c on t1.CustomerId=c.ID

Or you can use PIVOT

select c.username, t.* from
(
select * from 
(select * from ord
) src
pivot
( sum(Total) FOR Month IN ([1],[2],[3])
) pvt
) as t join Customer c on t.CustomerId=c.ID

edited Oct 03 '18 at 08:39

answered Oct 03 '18 at 07:43

Zaynul Abadin Tuhin

31,407
5
33
63

This doesn't work. The top solution returns multiple instances of the original username. Eg it hasn't summed the totals and grouped by username. – rollsch Oct 03 '18 at 08:26

score 0 · Answer 3 · answered Oct 03 '18 at 07:37

Something like this:

SELECT DATENAME(Month, (orderTable.PaidDateUTC)) MonthName, 
   customerTable.Username Username, 
   SUM(orderTable.OrderTotal) TotalMay
FROM "Order" orderTable
JOIN Customer customerTable ON orderTable.CustomerId = customerTable.Id
GROUP BY DATENAME(Month, (orderTable.PaidDateUTC)), Username

You just need to move the month name from the WHERE clause to the GROUP BY.

score 0 · Answer 4 · answered Oct 03 '18 at 09:15

I would simply do JOIN with conditional aggregation :

SELECT c.Username, 
       SUM(CASE WHEN o.Month = 1 THEN o.Total ELSE 0 END) AS [Month1Total],
       SUM(CASE WHEN o.Month = 2 THEN o.Total ELSE 0 END) AS [Month2Total],
       SUM(CASE WHEN o.Month = 3 THEN o.Total ELSE 0 END) AS [Month3Total],
       . . . 
FROM Customer C INNER JOIN
     Order o
     ON o.CustomerId = c.id
GROUP BY c.Username;

Produce a per customer per month sales report via SQL eg group by two columns and sum the third

4 Answers4