Question 22030 in Mysql Question posted in
The official MySQL documentation can be found here.

Sum all count for every timestamp – Mysql

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
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search