Sum all count for every timestamp-CodePudding

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

CodePudding user response：

Sample table:

MariaDB [databasename]> create table quux (count integer, timestamp varchar(12));
Query OK, 0 rows affected (0.010 sec)

MariaDB [databasename]> insert into quux values (1,'10:05'),(2,'10:06'),(3,'10:07'),(1,'10:08'),(2,'10:09'),(3,'10:10');
Query OK, 6 rows affected (0.002 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query to get a cumulative sum:

MariaDB [databasename]> set @cumsum:=0; select (@cumsum := @cumsum   count) as count, timestamp from quux;
Query OK, 0 rows affected (0.000 sec)

 ------- ----------- 
| count | timestamp |
 ------- ----------- 
|     1 | 10:05     |
|     3 | 10:06     |
|     6 | 10:07     |
|     7 | 10:08     |
|     9 | 10:09     |
|    12 | 10:10     |
 ------- ----------- 
6 rows in set (0.000 sec)

For this example,

MariaDB [databasename]> select version();
 --------------------------------------- 
| version()                             |
 --------------------------------------- 
| 10.9.3-MariaDB-1:10.9.3 maria~ubu2204 |
 --------------------------------------- 
1 row in set (0.000 sec)

CodePudding user response：

Try window aggregate SUM() OVER:

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