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.

Ike Walker · Accepted Answer · 2013-04-04 15:12:22Z

109

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 Apr 4, 2013 at 15:12

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mpen Over a year ago

Can't you just do sum(product_id=1)? product_id=1 is a boolean expression; it will naturally return 1 or 0 without the switch case.

Ike Walker Over a year ago

@Mark yes that would work as well, and may be nominally faster, but I find the solution as written to be more readable.

mnutsch Over a year ago

Is there a way to do this dynamically without knowing the potential values of 'product_id'?

Taryn · Accepted Answer · 2013-04-04 15:15:38Z

31

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

answered Apr 4, 2013 at 15:15

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

mpapec Over a year ago

Possibly a more appealing case suitable for counting, count(case product_id when X then 1 end)

d'alar'cop · Accepted Answer · 2013-04-04 15:11:51Z

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.

answered Apr 4, 2013 at 15:11

d'alar'cop

2,3531 gold badge15 silver badges18 bronze badges

1 Comment

Christy Herron Over a year ago

thanks, this solution did work, but it took a few seconds. Ike's solution was very fast by comparison, but thank you for your answer anyway @d'alar'cop

Maximilian Fixl · Accepted Answer · 2022-03-22 14:12:55Z

0

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

answered Mar 22, 2022 at 14:12

Maximilian Fixl

7186 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL count columns on specific value

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related