count of rows in table day wise

Question

I need to get a count of rows in a table day wise.

The table looks like below:

Table ABC:

f_id|reg_date
1|2020-09-08
2|2020-09-12
3|2020-10-01
4|2020-09-07
5|2020-09-08
6|2020-09-09

Expected output if I am running query saying I want the count of rows till 2020-09-15 since a week before of 15th:

count|date
1|2020-09-07
3|2020-09-08
4|2020-09-09
4|2020-09-10
4|2020-09-11
5|2020-09-12
5|2020-09-13
5|2020-09-14

I am not sure how to get this above output.

The rouble with the date range and group by is just giving me the the count of rows for that date, not a total of the count till that date. For example group by date gives me: 1|2020-09-07, 2|2020-09-08 and so on.

Answer 1

One solution would be this:

SELECT DISTINCT
(SELECT COUNT(t2.f_id) FROM t t2 WHERE t2.reg_date <= dates.d) AS running_total
, dates.d
FROM t
RIGHT JOIN dates ON t.reg_date = dates.d
ORDER BY dates.d;

- see it working live in this sqlfiddle

As you can see, you need to provide the dates for which there is no data in your table in some way, if you really want to include them in your result. The easiest way is to have a table with some dates in it. Another way could be some views with fancy math.

Anyway, what you have to look out for in this solution is that you need to specify a column in the COUNT() function. With COUNT(*) it would count the NULL values, too.

There are other ways to solve this as well, especially with MySQL 8.0. Unfortunately I don't have time right now, but maybe I will find the time later. Or someone else adds another answer.

EDIT: to create for example 1000 dates starting from an arbitrary date (in this example the current date) on the fly, you could for example use this:

SELECT CURDATE() + INTERVAL c*100+b*10+a DAY AS thousand_days
FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3
ORDER BY thousand_days;