1

First, this was my first question concerning that issue : SQL request to group and sum numbers by their day of creation

Now, imagine I want to make it more complex, after previous operations and thanks to answers, I have results grouped by day, so each entries of the day are added. I have this :

1 | 200 | 2010-01-01
2 | 100 | 2010-01-01

Transformed into this :

1 | 300 | 2010-01-01

That's already pretty good, but what if I want the amount field to increment each time ?

1 | 300 | 2010-01-01
2 | 200 | 2010-01-02

Will become :

1 | 300 | 2010-01-01
2 | 500 | 2010-01-02
x | (previous amount + this amount) | this date

The sql query I got thanks to answers to my previous question : 

select sum(amount), to_char(date, 'YYYY-MM-DD') 
from mytable 
group by to_char(date, 'YYYY-MM-DD') 
order by sum(amount) ASC;
Community
  • 1
  • 1
Alex
  • 140
  • 11

3 Answers3

3

A running sum can be done using windowing functions:

select sum(amount) over (order by date asc), 
       to_char(date, 'YYYY-MM-DD') 
from mytable 
order by date ASC;

If you want the running sum over the aggregated result, just put it into a sub-query:

select sum(day_total) over (order by date) as running_total,
       day_total, 
       date
from (
  select sum(amount) as day_total, 
         date
  from mytable 
  group by date
) t
order by date

If the column date is actual of the data type date, then there is no reason to use to_char() on it. Btw: it's not a terribly good idea to use reserved words (date) for column names.

The important thing here is the order by as part of the over definition.

More details in the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html

  • 1
    This query gets all record. It doesnt group and sum the amount – Orhan Cinar Aug 02 '12 at 11:27
  • This works partialy, as the sum by day is not done anymore. I get valid running sum, but if I have two entries by day, the sum is the same amount for those. `1|300|2010-01-01` `2|300|2010-01-01` `3|600|2010-01-02` instead of : `1|300|2010-01-01` `2|600|2010-01-02` needless to say this is way over my current sql skills ^^' – Alex Aug 02 '12 at 11:28
1

That's a running total.

Have a look at the following question. The answer by Quassnoi considers PostgreSQL.

Community
  • 1
  • 1
Andreas
  • 1,751
  • 2
  • 14
  • 25
  • Thank you for your clarification, I'm not sure I understand what is said there, maybe I'll just use a simpler, but sloppy way to do it. – Alex Aug 02 '12 at 11:31
0

try this:

 select date,(select sum(amount)
                    from mytable11 T1 
                    where T1.date<=T2.date
                    )as cum_sum
from mytable11 T2
group by date
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • I'm sorry I didn't manage to adapt your query, it said cum_sum did not exist. The above solution worked though, i'm guessing it's almost the same mechanism as yours. – Alex Aug 02 '12 at 11:43
  • `[date]` is not a valid syntax for Postgres –  Aug 02 '12 at 11:47
  • @Alex: I have done this in sql server, sorry I dont know postgressql – Joe G Joseph Aug 02 '12 at 11:50
  • I know, I just translated my var to english so it's understood on stackoverflow, my real var is not date. – Alex Aug 02 '12 at 11:50