mysql - Mysql get count on multiple columns
问题描述
If I have a table showing entries on doors looking like this:
+---+---------------------+------+------+
|id | entry_time | user | door |
+---+---------------------+------+------+
|1 | 2018-08-28 12:31:58 | 12 | A |
|2 | 2018-08-28 14:39:08 | 12 | A |
|3 | 2018-08-28 15:22:36 | 12 | B |
|4 | 2018-09-10 10:06:59 | 14 | C |
|5 | 2018-09-11 09:21:57 | 14 | A |
|6 | 2018-09-11 09:44:25 | 14 | A |
|7 | 2018-09-11 10:24:55 | 14 | B |
|8 | 2018-09-11 13:50:14 | 14 | C |
|9 | 2018-09-12 11:57:11 | 14 | A |
|10 | 2018-09-14 08:26:30 | 14 | B |
|11 | 2018-09-15 10:45:07 | 17 | B |
|12 | 2018-09-15 11:01:12 | 10 | C |
|13 | 2018-09-15 11:06:02 | 8 | A |
|14 | 2018-09-15 11:41:13 | 21 | B |
+---+---------------------+------+------+
How do I construct my query to get to this?
+------------+---------+-------+--------+--------+--------+
| entry_hour | entries | users | door A | door B | door C |
+------------+---------+-------+--------+--------+--------+
| 0800-0900 | 1 | 1 | 0 | 1 | 0 |
| 0900-1000 | 2 | 1 | 2 | 0 | 0 |
| 1000-1100 | 3 | 2 | 0 | 2 | 1 |
| 1100-1200 | 4 | 3 | 2 | 1 | 1 |
| 1200-1300 | 1 | 1 | 1 | 0 | 0 |
| 1300-1400 | 1 | 1 | 0 | 0 | 1 |
| 1400-1500 | 1 | 1 | 1 | 0 | 0 |
| 1500-1600 | 1 | 1 | 0 | 1 | 0 |
+------------+---------+-------+--------+--------+--------+
I know how to get the first and second column but can't get my head around the other ones. Here's what I have so far:
SELECT
CONCAT(TIME_FORMAT(entry_time, '%H00'),'-',TIME_FORMAT(DATE_ADD(entry_time,INTERVAL 1 HOUR), '%H00')) AS entry_hour,
COUNT(id) AS 'entries'
FROM
test
GROUP BY entry_hour
ORDER BY entry_hour;
Here's the CREATE statement if anyone want to take a stab at it:
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`entry_time` datetime DEFAULT NULL,
`user` int(2) DEFAULT NULL,
`door` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `tmp_test` VALUES ('1', '2018-08-28 12:31:58', '12', 'A'), ('2', '2018-08-28 14:39:08', '12', 'A'), ('3', '2018-08-28 15:22:36', '12', 'B'), ('4', '2018-09-10 10:06:59', '14', 'C'), ('5', '2018-09-11 09:21:57', '14', 'A'), ('6', '2018-09-11 09:44:25', '14', 'A'), ('7', '2018-09-11 10:24:55', '14', 'B'), ('8', '2018-09-11 13:50:14', '8', 'C'), ('9', '2018-09-12 11:57:11', '14', 'A'), ('10', '2018-09-14 08:26:30', '14', 'B'), ('11', '2018-09-15 10:45:07', '17', 'B'), ('12', '2018-09-15 11:01:12', '10', 'B'), ('13', '2018-09-15 11:06:02', '8', 'A'), ('14', '2018-09-15 11:41:13', '21', 'B');
COMMIT;
解决方案
在 CASE WHEN 中使用条件聚合
SELECT
CONCAT(TIME_FORMAT(entry_time, '%H00'),'-',TIME_FORMAT(DATE_ADD(entry_time,INTERVAL 1 HOUR), '%H00')) AS entry_hour,
COUNT(id) AS 'entries',
count(case when door='A' then id end) as doorA,
count(case when door='B' then id end) as doorB,
count(case when door='C' then id end) as doorC
FROM
test
GROUP BY entry_hour
ORDER BY entry_hour
推荐阅读
- html - 全屏和地理位置按钮在我的网络地图中不起作用
- ios - 上下文闭包类型 '(Data?, URLResponse?, Error?) -> Void' 需要 3 个参数,但 1 在闭包主体中使用
- android-fragments - 将 Xamarin.Forms 片段嵌入导致“孩子已经有父母”崩溃的片段中
- javascript - javascript on form 选项传递两个值,一个用于隐藏/可见功能,第二个用于数据值
- r - 代码不再适用于在 R 中的 vegan 中更改 metaMDS 中点的形状/颜色
- node.js - 使用 fs.writeFile 时如何不重复数据?
- c# - 在 .NET 4.5.2 项目中成功安装 NuGet 包没有参考
- python - 在 Python 中,R 的函数“复制”的等价物是什么?
- wordpress - API 的反向代理
- c# - 使用 Docker 创建和播种数据库时出错