CODEWITHSUNDEEP
sqlpostgresql
neocode27
neocode27

Reputation: 25

How to INSERT with cumulative add from a previous row?

In the following table

----------------------------
|  id  |    day   | count  |
----------------------------
    1   2013-01-01    10
    1   2013-01-05    20
    1   2013-01-08    45

the second and third row the count column is cumulative i.e. 20 = (10 from first row + 10 additional count) and 45 ( 20 from second row + 25 additional count). How can the second and third rows (and further) be inserted with cumulative add in Postgresql ?

Note: The additional count is read from a variable in a program. So the aim is to store this value in the 'count' column in Postgresql, but also add it with the 'count' found by last entry in ascending date order.

Upvotes: 0

Views: 141

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50127

If the problem is how the insert statement with a select could look like:

insert into x(id, day, count) 
select 1, current_timestamp, 
coalesce((select max(count) from x), 0) + 10;

But this is not necessarily the best way to solve the problem.

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

Since you don't say where does the additional count come from I assume there is an additional count column:

select *,
    sum(additional_count) over(order by "day")  "count"
from t
order by "day"

The sum function as a window function does a running total. It is a window function when it uses the over clause.

Upvotes: 1

Related Questions