【Question】

I have a SQL table which has 3 columns like this:

entry_time  |  A  |  B  |

-------------------------------------------

2015-06-05 18:44:56 |  10  |  8  |

2015-06-06 16:04:36 |  -5  |  -2  |

2015-06-07 10:01:14 |  3  |  1  |

I want to get the sum of A and B columns up to particular given time stamp like below:

status at given date | A  | B  |

-------------------------------------------

2015-06-05 23:59:59 |  10  |  8  |

2015-06-06 23:59:59 |  5  |  6  |

2015-06-07 23:59:59 |  8  |  7  |

As I am giving the date I want totals from beginning to end without its first records date. Is there any way to solve this?

A solution:

select

entry_time,

a_sum as A,

b_sum as B from(

 select

 entry_time,

 @A_sum:= @A_sum + A as a_sum,

 @B_sum:= @B_sum + B as b_sum

 from table_name,(select @A_sum:=0,@B_sum:=0)x

order by entry_time

)x

【Answer】

It’s easy to solve it in SQL with a variable. Here’s an alternative. You can handle it in SPL (Structured Process Language) which enables intuitive inter-row computation:

A1: Retrieve data ordered by entry_time in SQL.

A2: Calculate the accumulated sums between rows and reassign values to column A and column B.