MySQL计算特定值的列

发布于04月04日

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.

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

MySQL计算特定值的列

推荐答案

Mysql相关问答推荐

奇怪的MySQL SELECT循环；但不是\G

无法连接到扩展坞MySQL Unix套接字

PythonAnywhere中SSH到MySQL数据库无限期挂起，SSH正确，workbench可以完美连接

MySQL - 如何根据单列查找重复行？

使用索引进行查询优化

任何值的 SQL WHERE 子句？

将 AVG 与 HAVING 结合使用

Mysql insert with loop out of select 语句

MySQL 查询组按日期(12 小时间隔)

如何通过未知列中的唯一值有效地更新 MySQL 行

计算每个用户在第二个表中有多少条记录

函数 mysql_real_escape_string 的 PDO 类似功能是什么？

字符ي和ی以及波斯语的区别 - Mysql

MySQL：按字段排序，将空单元格放在末尾

PHP 判断 NULL

在 Postgresql 中模拟 MySQL 的 ORDER BY FIELD()

如何将 MySQL 查询结果存储在另一个表中？

应该使用什么列类型将序列化数据存储在 mysql 数据库中？

在 MySQL 中存储 IPv6 地址

为什么 GRANT 在 MySQL 中不起作用？