Mysql get count on multiple columns

Question

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;

If appropriate, consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Mar 5, 2019 at 7:48

Fahmi · Accepted Answer · 2019-03-05 07:25:20Z

2

use conditional aggregation with 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

answered Mar 5, 2019 at 7:25

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

1 Comment

jolle Over a year ago

Thanks! There's one pice missing that I got from a colleague: COUNT(DISTINCT user) as 'users',

Collectives™ on Stack Overflow

Mysql get count on multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related