Sum all count for every timestamp

Question

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

This is "cumulative sum". The solution depends on **precise** MySQL version. — Akina, Nov 28 '22 at 17:25
You could use a counter and order by timestamp if this works for you? https://stackoverflow.com/a/51764189/1363190 — Michael Krikorev, Nov 28 '22 at 18:18

score 0 · Answer 1 · answered Nov 28 '22 at 20:07

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)

tinazmu · Accepted Answer · 2022-11-29T00:35:21.013

0

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

edited Nov 29 '22 at 00:35

answered Nov 28 '22 at 23:22

tinazmu

3,880
2
7
20

Select as accepted answer, then – tinazmu Nov 29 '22 at 08:25

Sum all count for every timestamp

2 Answers2