count of rows in table day wise

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply