CODEWITHSUNDEEP
mysqlsqldatetimegroup-bycount
ip.
ip.

Reputation: 130

MySQL group_by+group_concat missing dates

My schema is:

create table calendar(day date);
insert into calendar values('2020-09-01'), ('2020-09-02'), ('2020-09-03');
create table messages(id integer primary key auto_increment, user_id integer, created_at date);
insert into messages(user_id, created_at) values(1, '2020-09-01'), (1, '2020-09-01'), (2, '2020-09-01'), (2, '2020-09-03')

I need to get count of messages for every user for every date in range:

|user_id|data |
|1      |2,0,0|
|2      |1,0,1|

my query is:

SELECT r.user_id, group_concat(r.cnt ORDER BY r.day) AS data 
FROM 
(SELECT calendar.day, user_id, count(messages.id) cnt
 FROM calendar LEFT JOIN messages ON date(messages.created_at) = calendar.day
 WHERE calendar.day BETWEEN '2020-09-01' AND '2020-09-03'
 GROUP BY user_id, date_format(calendar.day, '%Y-%m-%d')) r
GROUP BY r.user_id;

what I exactly get:

|user_id|data |
|NULL   |0    |
|1      |2    |
|2      |1,1  |

I don't know how to group it right, please help me. SQL Fiddle

Upvotes: 1

Views: 47

Answers (1)

GMB
GMB

Reputation: 222722

You can cross join the list of distinct users with the calendar to generate all possible combinations, then bring the table with a left join.

The rest is just aggregation logic, which you already have in place:

select user_id, group_concat(cnt order by day) data
from (
    select u.user_id, c.day, count(m.id) cnt
    from calendar c
    cross join (select distinct user_id from messages) u
    left join messages m 
        on  m.created_at >= c.day and m.created_at < c.day + interval 1 day
        and m.user_id = u.user_id
    where c.day between '2020-09-01' and '2020-09-03'
    group by u.user_id, c.day
) t
group by user_id

In a real life situation, you would probably have a separate table to store the users, that you can directly use instead of selecting the distinct values from the messages table.

Demo on DB Fiddle:

user_id | data 
------: | :----
      1 | 2,0,0
      2 | 1,0,1

Upvotes: 1

Related Questions