CODEWITHSUNDEEP
sql
Bodul
Bodul

Reputation: 185

How to construct SQL query for this...?

I would like to do some math operation but for each row at the time.

For example:

A         B        C         D
-------------------------------
100      -50       =50      20160101 
100       0        =150     20160102
100      -50       =100     20160103

So basically column C would always be sum of all past A +(B) columns, but not future ones. Does anyone have idea on how to achieve this in SQL?

I can do this in code, but I would like to do this in SQL and just show result in table.

P.S. my english is not the best, so feel free to ask if I was not clear enough.

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is called a cumulative or running sum. The normal method uses ANSI standard window functions:

select a, b,
       sum(a + b) over (order by d) as c,
       d
from t;

If your version of SQL doesn't support window functions, then you can use a correlated subquery (performance would generally be much worse):

select a, b,
       (select sum(a + b) from t t2 where t2.d <= t.d) as c,
       d
from t;

Upvotes: 4

Related Questions