0

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!

  • The second output can be "pivoted" to get the first output. – Rick James Jan 23 '19 at 17:48
1
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

| improve this answer | |
  • I have no words to express my gratitude. Thanks very much @Akina! – TJ is too short Jan 23 '19 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.