New answers tagged mysql-5.5
0
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 ...
0
It can be, for example,
SELECT SUM(dates.a_date >= test.on_date AND (dates.a_date < 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 ...
Top 50 recent answers are included
