How to create a count of values in mysql grouped by date?

Question

I have a table called users with columns infected (0 || 1 || NULL) and date_checkin (mysql timestamp), absence (0 || 1 || NULL).

I want to create a statistic, where I can see, how many guests have the infected value 0, 1 or Zero, same for absence grouped by the date.

Currently I have the amount of guests:

select count(`id`), date(`date_checkin`) as day
from `users` 
group by day

I want my result to look like

| all | infected | not infected | not tested | absence | not absence |     day    |
|  10 |    5     |       2      |     1      |    2    |      0      | 2021-05-26 |
| ....

infected 1 = infected
infected 0 = not infected
infected NULL = not tested
absense 1 = absence
absence 0 = not absence
absence NULL = this value does not matter

Here is some sample data:

|  id  |  date_checkin         | inf.  |  abs.    |
---------------------------------------------------
|  42  |  2021-05-04 23:20:11  |  0    |   NULL   |
|  43  |  2021-05-04 23:20:14  |  0    |   NULL   |
|  44  |  2021-05-04 23:20:18  |  NULL |   1      |
|  45  |  2021-05-04 23:20:21  |  NULL |   1      |
|  46  |  2021-05-04 23:20:24  |  1    |   NULL   |
|  47  |  2021-05-04 23:20:28  |  0    |   NULL   |
|  48  |  2021-05-04 23:20:31  |  0    |   NULL   |
|  49  |  2021-05-04 23:20:34  |  1    |   NULL   |
|  50  |  2021-05-05 07:55:15  |  0    |   NULL   |
|  51  |  2021-05-05 11:56:41  |  NULL |   1      |
|  52  |  2021-05-05 12:55:12  |  0    |   NULL   |
|  53  |  2021-05-09 11:13:12  |  NULL |   1      |
|  54  |  2021-05-17 17:33:52  |  0    |   NULL   |
|  55  |  2021-05-17 17:34:42  |  0    |   NULL   |
|  56  |  2021-05-18 11:12:31  |  0    |   NULL   |
|  57  |  2021-05-18 15:09:36  |  0    |   NULL   |
|  58  |  2021-05-18 15:20:59  |  0    |   NULL   |
|  59  |  2021-05-18 15:21:16  |  0    |   NULL   |
|  62  |  2021-05-18 15:25:40  |  0    |   NULL   |
|  63  |  2021-05-18 15:29:33  |  0    |   NULL   |
|  64  |  2021-05-18 16:02:02  |  0    |   NULL   |
|  65  |  2021-05-18 16:07:33  |  1    |   NULL   |
|  66  |  2021-05-18 16:07:51  |  0    |   NULL   |
|  67  |  2021-05-18 16:09:28  |  NULL |   1      |
|  68  |  2021-05-18 16:12:12  |  NULL |   1      |
|  69  |  2021-05-18 16:12:31  |  NULL |   1      |
|  70  |  2021-05-18 16:12:54  |  NULL |   1      |
|  71  |  2021-05-18 16:22:03  |  NULL |   1      |
|  72  |  2021-05-18 16:22:26  |  NULL |   1      |
|  73  |  2021-05-19 11:04:27  |  NULL |   1      |
|  79  |  2021-05-19 12:27:31  |  NULL |   1      |
|  80  |  2021-05-21 14:28:19  |  NULL |   1      |
|  81  |  2021-05-21 14:28:30  |  NULL |   1      |
|  82  |  2021-05-21 14:34:39  |  0    |   NULL   |
|  83  |  2021-05-21 14:35:47  |  NULL |   1      |
|  84  |  2021-05-21 14:36:54  |  NULL |   1      |
|  85  |  2021-05-21 14:38:26  |  0    |   NULL   |
|  86  |  2021-05-21 14:39:38  |  0    |   NULL   |
|  87  |  2021-05-21 14:39:39  |  0    |   NULL   |
|  88  |  2021-05-21 14:39:39  |  0    |   NULL   |
|  89  |  2021-05-21 14:39:42  |  0    |   NULL   |

Please add sample input table data to your question, along with the exact output you want to see. You want some kind of pivot query, but the previous duplicate link is probably not close enough to the answer to this question. — Tim Biegeleisen, May 26 '21 at 10:16

Majid Karimizadeh · Accepted Answer · 2021-05-26T10:35:08.390

1

You can use below query:

SELECT 
    COUNT(`id`) AS counts,
    SUM(CASE WHEN infected = '1' THEN 1 ELSE 0 END) AS infected,
    SUM(CASE WHEN infected = '0' THEN 1 ELSE 0 END) AS not_infected, 
    SUM(CASE WHEN infected IS NULL THEN 1 ELSE 0 END) AS not_tested,
    SUM(CASE WHEN absence = '1' THEN 1 ELSE 0 END) AS absence,
    SUM(CASE WHEN absence = '0' THEN 1 ELSE 0 END) AS not_absence,
    DATE(`date_checkin`) AS selected_day
FROM 
    users
GROUP BY 
    selected_day;

edited May 26 '21 at 10:35

answered May 26 '21 at 10:28

Majid Karimizadeh

98
1
3
10

You are treating the `1` and `0` as string. These are `tinyint` values (I did not mention that in my question). As they are no strings, will the SUM part change a little bit? It is working as it is but `IF 1 THEN 1 ELSE 0` is redundant. – Nick Rick May 26 '21 at 10:40

How to create a count of values in mysql grouped by date?

1 Answers1