MySQL – How to Count Rows in Table Day Wise

date mathMySQLmysql-5.5

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.

Best Answer

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;

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

MySQL – Rolling Count of Total Transactions Over Time

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d) 
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1) 
from ( 
   select distinct year(d) as y, week(d) as w 
   from transactions
) as x 
join transactions y 
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w ) 
group by x.y, x.w;  

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w 
from transactions

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.

Best Answer

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

MySQL – Rolling Count of Total Transactions Over Time

Related Question