SUM of products prices by cateogry as columns in MySQL

Question

I have 3 tables, customers, products, categories as follow :

customers :

id      last_name
-----------------
100     Terry
200     molly
300     John

products :

id  name        description     price   category_id     customer_id
-------------------------------------------------------------------
1   product1    lorem ipsum     150     10              100
2   product2    lorem ipsum     100     10              100
3   product3    lorem ipsum     300     20              200

categories :

id  name
---------
10  bags
20  toys
30  phones

The desired result :

customerID  customerLastName    bags    toys    phones
100         Terry               250     0       0
200         Molly               0       300     0
300         John                0       0       0

The desired result as you can see is the SUM of the products prices by category. so for the first customer, he only has products related to the category bags, so we should have a bags column with the SUM of the products price which is (100 for product1 and 150 for product2, which is 250 in total).

And if the customer doesn't have any product in a category, the value should be 0 in that category column.

I have tried this :

select 
customers.id as customerID, 
customers.last_name as customerLastName, 
SUM(CASE WHEN products.category_id = 10 THEN 1 ELSE NULL END) AS 'bags',
SUM(CASE WHEN products.category_id = 20 THEN 1 ELSE NULL END) AS 'toys',
SUM(CASE WHEN products.category_id = 30 THEN 1 ELSE NULL END) AS 'phones'
from customers
left join products on products.customer_id = customers.id
left join categories on products.category_id = categories.id
group by customers.id

But I get this result :

customerID  customerLastName    bags    toys    phones
100         Terry               2       0       0
200         Molly               0       1       0
300         John                0       0       0

score 2 · Accepted Answer · answered Nov 26 '18 at 09:23

2

Change THEN 1 with THEN products.price in order to aggregate price values. Also, if you don't specify ELSE NULL, it will be by default NULL only.

select 
  customers.id as customerID, 
  customers.last_name as customerLastName, 
  SUM(CASE WHEN products.category_id = 10 THEN products.price END) AS 'bags',
  SUM(CASE WHEN products.category_id = 20 THEN products.price END) AS 'toys',
  SUM(CASE WHEN products.category_id = 30 THEN products.price END) AS 'phones'
from customers
left join products on products.customer_id = customers.id
left join categories on products.category_id = categories.id
group by customers.id

answered Nov 26 '18 at 09:23

Madhur Bhaiya

28,155
10
49
57

I totally missed that, thanks, can I ask if there is a way to do this without doing a `CASE WHEN` for each category manually ? – Dwix Nov 26 '18 at 09:25
@dwix if there are more categories; it basically means a Pivot Table problem. In case of Dynamic number of columns, I recommend handling this in Application code. Still, you may refer this question: https://stackoverflow.com/questions/7674786/mysql-pivot-table – Madhur Bhaiya Nov 26 '18 at 09:26
1

Done, I had to wait for a few minutes before I can accept. – Dwix Nov 26 '18 at 09:45

SUM of products prices by cateogry as columns in MySQL

1 Answers1