'MySQL count columns on specific value

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.

mysql


Solution 1:[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

Solution 2:[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

Solution 3:[3]

See if this works:

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

Cheers. n.b. there may be slightly nicer ways to achieve the equivalent result.

Solution 4:[4]

This is a very old question, but possibly this is another variant for other searchers.

Since MySQL 4.0 if() can also be used at this point in combination with sum() and ifnull(). The ifnull() function ensures that if there are no rows, a 0 is output per column.

select user_id,
  ifnull(sum(if(product_id = 1, 1, 0)), 0) as prod_1_count,
  ifnull(sum(if(product_id = 2, 1, 0)), 0) as prod_2_count,
  ifnull(sum(if(product_id = 3, 1, 0)), 0) as prod_3_count,
  ifnull(sum(if(product_id = 4, 1, 0)), 0) as prod_4_count
from your_table
group by user_id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ike Walker
Solution 2 Taryn
Solution 3 d'alar'cop
Solution 4 Maximilian Fixl

Related Questions