mysql 计数列,MySQL对特定值计数列

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.

Can anyone help out me here?

thanks you

解决方案

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