CODEWITHSUNDEEP
mysql
Christy Herron
Christy Herron

Reputation: 714

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.

Upvotes: 51

Views: 72014

Answers (4)

Maximilian Fixl
Maximilian Fixl

Reputation: 688

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

Upvotes: 0

Taryn
Taryn

Reputation: 247720

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

Upvotes: 31

Ike Walker
Ike Walker

Reputation: 65547

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

Upvotes: 108

d'alar'cop
d'alar'cop

Reputation: 2365

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.

Upvotes: 3

Related Questions