how can I get a running total by Date in mySQL?

Question

I wanna get a running total.

I could count records by date(it looks little complicated...anyway) and wanna get a running total with this result. but it has an error in syntax as below.

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''tb' inner join tb as 'otb' on date(otb.date) <= date(tb.date) group by tb.date' at line 7

and my code is:

select 
tb.date, tb.counts, sum(otb.counts)
from
(select date(table.regdate) as date, count(*) as counts 
from table
where date(table.regdate) between '2020-01-01' and '2020-01-05'
group by date(table.regdate)
order by regdate) as 'tb' 
inner join tb as 'otb' on date(otb.date) <= date(tb.date)
group by tb.date, tb.counts 
order by tb.date;

a select query is working in this code:

(select date(table.regdate) as date, count(*) as counts 
 from table
 where date(table.regdate) between '2020-01-01' and '2020-01-05'
 group by date(table.regdate)
 order by regdate)

is work without any problem. And result table is as below

+------------+-------+
| 2020-01-01 | 20    |
| 2020-01-02 | 19    |
| 2020-01-03 | 8     |
| 2020-01-04 | 5     |
| 2020-01-05 | 9     |
+------------+-------+

but I wanna get running total by date like this

+------------+-------+
| 2020-01-01 | 20    |
| 2020-01-02 | 39    |
| 2020-01-03 | 47    |
| 2020-01-04 | 52    |
| 2020-01-05 | 61    |
+------------+-------+

table is a reserved word - do you actually have a table called table? Identifiers such as the aliases tb and otb should be backiticked rather than quoted (if quoted at all) — P.Salmon, Jan 08 '20 at 08:59
And if you search for mysql running total you will find many example how to do this — P.Salmon, Jan 08 '20 at 09:05

score 0 · Answer 1 · answered Jan 08 '20 at 12:22

You can use window functions. Assuming the second query is what you are talking about:

select date(t.regdate) as date, count(*) as counts,
       sum(count(*)) over (order by date(t.regdate)) as running_count
from table t
where date(t.regdate) between '2020-01-01' and '2020-01-05'
group by date(table.regdate)
order by date;

how can I get a running total by Date in mySQL?

1 Answers1