[SOLVED] MySQL count columns on specific value

Issue

I have the following db table, and I would like to be able to count the instance of sales of certain products per salesperson.

|------------|------------|------------|
|id          |user_id     |product_id  |
|------------|------------|------------|
|1           |1           |2           |
|2           |1           |4           |
|3           |1           |2           |
|4           |2           |1           |
|------------|------------|------------|

I would like to able to create a result set like the following;

|------------|-------------|------------|------------|------------|
|user_id     |prod_1_count |prod_2_count|prod_3_count|prod_4_count|
|------------|-------------|------------|------------|------------|
|1           |0            |2           |0           |1           |
|2           |1            |0           |0           |0           |
|------------|-------------|------------|------------|------------|

I am creating graphs with this data, and once again (as earlier today) I am unable to count the column totals. I have tried;

SELECT user_id, 
(SELECT count(product_id) FROM sales WHERE product_id = 1) AS prod_1_count,
(SELECT count(product_id) FROM sales WHERE product_id = 2) AS prod_2_count,
(SELECT count(product_id) FROM sales WHERE product_id = 3) AS prod_3_count,
(SELECT count(product_id) FROM sales WHERE product_id = 4) AS prod_4_count 
FROM sales GROUP BY user_id; 

I can see why this doesn’t work, because for each bracketed SELECT the user_id doesn’t match the external user_id in the main SELECT statement.

Solution

You can do this using SUM and CASE:

select user_id,
  sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
  sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
  sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
  sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
from your_table
group by user_id

Answered By – Ike Walker

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.