Search code examples
mysqlsqlsum

Sum all count for every timestamp

Hey I need some help with sql query I have this data

count Timestamp
1     10:05
2     10:06
3     10:07
1     10:08
2     10:09
3     10:10

I would like to obtain for each timestamp the total of count until that moment

count Timestamp
1     10:05
3     10:06
6     10:07
7     10:08
9     10:09
12    10:10

I tried a lot of things the last I have and blocked with it is :

select sum(count), timestamp 
from table
where timestamp > now() - interval 2 hour
group by date_format(timestamp, '%Y-%m-%d %h:%i')

But with that I dont get an increase value everytime for the sum, i guess it is because i use group by and the timestamp

I was thinking adding a join where I will do without the group by but how can I get the sum until the timestamp needed ? and not for all the table

select sum(count)
from table
Solution

  • Both 'count' and 'timestamp' are actually used in SQL language and maybe reserved for some usage - depending on your rdbms; consider renaming them to more meaningful ones.

    Try window aggregate SUM() OVER:

    SELECT
  count
, timestamp 
, sum(count) over (order by timestamp 
                       rows between unbounded preceding and current row) as CumCount
from table
where timestamp > now() - interval 2 hour