How to get sum of the count of rows till date from table in MySQL I have a table as below:
TABLE A:
id|  on_date |off_date
1 |2020-09-10|
2 |2020-09-09|
3 |2020-09-08|
4 |2020-09-09|2020-09-10
5 |2020-09-08|

I want to get the count of ids which are in on mode till a particular date (2020-09-11)
Expected output:
count|date
2    |2020-09-08 (because id 3 and 5 has on_date of 2020-09-08)
4    |2020-09-09 (because id 2,3 and 5 has on_date till 09-09-2020)
4    |2020-09-10 (because id 4 not counted because off_date is present and id 1 is added)

I am not really sure how to get this output.
It can be, for example,
SELECT SUM(dates.a_date &gt;= test.on_date AND (dates.a_date &lt; test.off_date OR test.off_date IS NULL)) `count`, 
       dates.a_date `date`
FROM ( SELECT on_date a_date FROM test 
       UNION 
       SELECT off_date FROM test WHERE off_date IS NOT NULL ) dates
CROSS JOIN test
GROUP BY 2
ORDER BY 2;

fiddle
dates subquery generats dates list when the amount of ON ids might have changed.
SUM tests each row against the conditions for particular date and counts the amount of matched rows (testing result is 1 while matching and 0 otherwise).
If some date is absent then look the row with closest lower date.