Sum of previous rows in mysql-CodePudding

I tried to adapt everything from Create a Cumulative Sum Column in MySQL to what I'm doing, but I can't seem to get it right. I am a newbie at MySQL and stackoverflow.com. My table looks like in the following example. I need to place a sum of previous rows of the AR column in each (1, 2, 3) column depending on time. So, in the "1" column I have to place a sum of all the previous AR cells but only when Time=01:00, in the "2" column I have to place a sum of all the previous AR cells but only when Time=02:00 and so on. The other cells from each (1, 2, 3) column should remain 0.00000.

My problem is that I can't find the proper syntax, considering that I can't find a way to replace the "id" from the examples posted on the specified topic.

LE: I also found this How to do a sum of previous rows in mysql but I have no idea what should I change in it to get the desired result. I simply don't understand where each notation comes from (e.g.: r.row1, t2.row1, requete r2, cumesum etc.)

Can someone help me, please?

Thank you!

Sample table:

| Time | AR | 1 | 2 | 3 |
|:-----|:---|:--|:--|:--|
|00:00 |0.12|0  |0  |0  |
|01:00 |0.16|0  |0  |0  |
|02:00 |0.13|0  |0  |0  |
|03:00 |0.19|0  |0  |0  |
|04:00 |0.11|0  |0  |0  |
|00:00 |0.15|0  |0  |0  |
|01:00 |0.34|0  |0  |0  |
|02:00 |0.56|0  |0  |0  |
|03:00 |0.67|0  |0  |0  |
|04:00 |0.92|0  |0  |0  |

Desired table:

| Time | AR | 1       | 2       | 3       |
|:-----|:---|:--------|:--------|:--------|
|00:00 |0.12|0        |0        |0        |
|01:00 |0.16|0.16     |0        |0        |
|02:00 |0.13|0        |0.13     |0        |
|03:00 |0.19|0        |0        |0.19     |
|04:00 |0.11|0        |0        |0        |
|00:00 |0.15|0        |0        |0        |
|01:00 |0.34|0.16 0.34|0        |0        |
|02:00 |0.56|0        |0.13 0.56|0        |
|03:00 |0.67|0        |0        |0.19 0.67|
|04:00 |0.92|0        |0        |0        |

CodePudding user response：

Frist, add a unique column to the table, Then you can use the same sql syntax that you found.

For example, Add the ID column(PRIMARY Key, auto increment)

 ---- ---------- ------ 
| ID | Time     | AR   |
 ---- ---------- ------ 
|  1 | 00:00:00 | 0.12 |
|  2 | 01:00:00 | 0.16 |
|  3 | 02:00:00 | 0.13 |
|  4 | 03:00:00 | 0.19 |
|  5 | 04:00:00 | 0.11 |
|  6 | 00:00:00 | 0.15 |
|  7 | 01:00:00 | 0.34 |
|  8 | 02:00:00 | 0.56 |
|  9 | 03:00:00 | 0.67 |
| 10 | 04:00:00 | 0.92 |
 ---- ---------- ------

the SQL:

set @arsum_1 := 0;
set @arsum_2 := 0;
set @arsum_3 := 0;

select t.Time, t.AR, ROUND(IFNULL(t1.A1, 0),2) as `1`, ROUND(IFNULL(t2.A2, 0), 2) as `2`, ROUND(IFNULL(t3.A3, 0),2) as `3` from time_sample t 
natural left join (select ID,  @arsum_1 := @arsum_1   AR as A1 from time_sample where Time='01:00') as t1 
natural left join (select ID, @arsum_2 := @arsum_2   AR as A2 from time_sample where Time='02:00') as t2 
natural left join (select ID, @arsum_3 := @arsum_3   AR as A3 from time_sample where Time='03:00') as t3 order by t.Time;

output:

 ---------- ------ ------ ------ ------ 
| Time     | AR   | 1    | 2    | 3    |
 ---------- ------ ------ ------ ------ 
| 00:00:00 | 0.12 | 0.00 | 0.00 | 0.00 |
| 00:00:00 | 0.15 | 0.00 | 0.00 | 0.00 |
| 01:00:00 | 0.16 | 0.16 | 0.00 | 0.00 |
| 01:00:00 | 0.34 | 0.50 | 0.00 | 0.00 |
| 02:00:00 | 0.13 | 0.00 | 0.13 | 0.00 |
| 02:00:00 | 0.56 | 0.00 | 0.69 | 0.00 |
| 03:00:00 | 0.19 | 0.00 | 0.00 | 0.19 |
| 03:00:00 | 0.67 | 0.00 | 0.00 | 0.86 |
| 04:00:00 | 0.11 | 0.00 | 0.00 | 0.00 |
| 04:00:00 | 0.92 | 0.00 | 0.00 | 0.00 |
 ---------- ------ ------ ------ ------