MySQL – How to Get Count and Percentage of Valid and Invalid Records by Day

MySQL

I'm struggling to write a query.

Basically I have this table:

id, valid, user_id, created_at
1,      1,       1, 2018-01-01 12:55:39
2,      0,       1, 2019-01-02 11:23:33
3,      0,       1, 2019-01-03 16:02:13
4,      1,       1, 2019-01-01 01:12:34
5,      1,       1, 2019-01-02 16:43:58
6,      0,       1, 2019-01-03 16:32:24
7,      1,       1, 2019-01-01 06:54:54
8,      1,       1, 2019-01-02 21:07:08
9,      0,       1, 2019-01-03 03:23:19
10,     1,       2, 2019-01-01 16:43:18
11,     1,       3, 2019-01-02 16:15:29
12,     0,       2, 2019-01-03 06:50:30
13,     0,       2, 2019-01-01 23:03:36
14,     0,       2, 2019-01-02 16:12:45
15,     0,       3, 2019-01-03 22:55:58
16,     1,       2, 2019-01-01 06:55:33
17,     1,       2, 2019-01-02 06:47:16
18,     0,       3, 2019-01-03 16:25:49
19,     1,       3, 2019-01-01 20:05:57
20,     1,       3, 2019-01-01 16:48:19

The result that I'm trying to achieve is getting how many valid records are in the table per day for a specific user.

The ideal result is to have this:

User_id 1

date,        valid, pvalid, invalid, pinvalid
2018-01-01,      3,   100%,       0,       0%
2018-01-02,      2,  66.6%,       1,    33.3%
2018-01-03,      0,     0%,       3,     100%

User_id 2

date,        valid, pvalid, invalid, pinvalid
2018-01-01,      2,  66.6%,       1,    33.3%
2018-01-02,      1,    50%,       1,      50%
2018-01-03,      0,     0%,       1,     100%

User_id 3

date,        valid, pvalid, invalid, pinvalid
2018-01-01,      2,   100%,       0,       0%
2018-01-02,      1,   100%,       0,       0%
2018-01-03,      0,     0%,       2,     100%

But if it's not possible, I can work with the data returned like this:

User_id 1

date,        valid, total, percentage
2018-01-01,      0,     0,         0%
2018-01-01,      1,     3,       100%
2018-01-02,      0,     1,      33.3%
2018-01-02,      1,     2,      66.6%
2018-01-03,      0,     3,       100%
2018-01-03,      1,     0,         0%

User_id 2

date,        valid, total, percentage
2018-01-01,      0,     1,      33.3%
2018-01-01,      1,     2,      66.6%
2018-01-02,      0,     1,        50%
2018-01-02,      1,     1,        50%
2018-01-03,      0,     1,       100%
2018-01-03,      1,     0,         0%

User_id 3

date,        valid, total, percentage
2018-01-01,      0,     0,         0%
2018-01-01,      1,     2,       100%
2018-01-02,      0,     0,         0%
2018-01-02,      1,     1,       100%
2018-01-03,      0,     2,       100%
2018-01-03,      1,     0,         0%

I've started with this query:

SELECT CONCAT(DAY(t.created_at), '-', MONTH(t.created_at), '-', YEAR(t.created_at)) as `day`
     , t.valid
     , COUNT(*) AS total
     , COUNT(*) / t1.cnt * 100 AS `percentage`
FROM table t
CROSS JOIN (SELECT COUNT(*) AS cnt FROM table) t1
WHERE t.user_id = 1
GROUP BY `day`, t.valid;

But this will calculate the percentage based on all records of the table, but I need to calculate the percentage on a per-day basis.

How can I calculate the percentage on a per-day basis?

I don't know if it's worth to mention, but after I have the query I will need to adapt it in order to be used in Laravel.

Thanks in advance!

Best Answer

SELECT user_id, 
       DATE(created_at) created_at, 
       SUM(valid=1) valid, 
       100*SUM(valid=1)/COUNT(*) pvalid, 
       SUM(valid=0) invalid, 
       100*SUM(valid=0)/COUNT(*) pinvalid
FROM test
GROUP BY user_id, DATE(created_at)
ORDER BY user_id, DATE(created_at);

fiddle

Related Question