GROUP BY date and empty data

Asked Feb 28 '21 at 13:35

Active Feb 28 '21 at 14:04

Viewed 246 times

I have table hits with columns created and user_id.

I want get stats hits count for last 30 days, GROUP BY day. But I have problem, because some days user dont have traffic.

And as a result, I do not see this day in the report.

How to get data for every day (with 0 hits), even where there is no hits?

My query:

SELECT user_id, toDate(created) as date, COUNT() as count
FROM hits
WHERE created > NOW() - INTERVAL 30 DAY
GROUP BY toDate(created), user_id

edited Feb 28 '21 at 14:04

asked Feb 28 '21 at 13:35

Alex

https://learnsql.com/blog/null-values-group-clause/ – Martin Feb 28 '21 at 13:38
What mysql version? If mysql8, you can use CTE to generate rows for every day. – etsuhisa Feb 28 '21 at 13:45
my database `clickhouse` – Alex Feb 28 '21 at 14:00
1

order by with fill https://stackoverflow.com/search?q=%5Bclickhouse%5DORDER+BY+WITH+FILL – Denny Crane Feb 28 '21 at 15:57

0 Answers0