MySQL count columns on specific value

Question

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.

Answer 1

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 

Answer 2

You are trying to pivot the data. MySQL does not have a pivot function so you will have to use an aggregate function with a CASE expression:

select user_id,   count(case when product_id = 1 then product_id end) as prod_1_count,   count(case when product_id = 2 then product_id end) as prod_2_count,   count(case when product_id = 3 then product_id end) as prod_3_count,   count(case when product_id = 4 then product_id end) as prod_4_count from sales group by user_id; 

See SQL Fiddle with Demo