CODEWITHSUNDEEP
sqlanalysisorders
Neykof
Neykof

Reputation: 49

SQL How to group data into separate month columns

So I'm running this query to get the name of the customer, total amount ordered, and number of orders they've submitted. With this query, I get their entire history from March to July, what I want is the name, march amount total/# of orders, april amount total/# of orders, may amount total/# of orders, ..... etc.


SELECT customer_name,MONTH(created_on), SUM(amount), COUNT(order_id)
FROM customer_orders
WHERE created_on BETWEEN '2020-03-01' AND '2020-08-01' 
GROUP BY customer_name, MONTH(created_on)

Upvotes: 1

Views: 1436

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If you want the values in separate columns, then use conditional aggregation:

SELECT customer_name,
       SUM(CASE WHEN MONTH(created_on) = 3 THEN amount END) as march_amount,
       SUM(CASE WHEN MONTH(created_on) = 3 THEN 1 ELSE 0 END) as march_count,
       SUM(CASE WHEN MONTH(created_on) = 4 THEN amount END) as april_amount,
       SUM(CASE WHEN MONTH(created_on) = 4 THEN 1 ELSE 0 END) as april_count,
       . . .
FROM customer_orders
WHERE created_on >= '2020-03-01' AND
      created_on < '2020-08-01' 
GROUP BY customer_name;

Notice that I changed the date filter so it does not include 2020-08-01.

Upvotes: 3

Related Questions