Re: Accumulated sums in SQL query

>
> OK I have a table named bank_account_movements containing two columns
=
> date and amount:
>
> date                               amount (in USD)
> -------------------------------------
> 2002-10-01                   20
> 2002-10-02                   30
> 2002-10-03                   -15
> 2002-10-04                   -5
> 2002-10-05                  -3
> 2002-10-06                    10
>
> my goal is to create a view from it adding an extra column named =
> balance!
>
> date                               amount (in USD)      balance
> -----------------------------------------------------------
> 2002-10-01                   20                             20
> 2002-10-02                   30                             50
> 2002-10-03                   -15                            35
> 2002-10-04                   -5                              30
> 2002-10-05                  -3                               27
> 2002-10-06                    10                             17
>
> The balance is 0+20=20, 0+20+30=50, 0+20+30-15=35 and so on...
> how would you write the SQL query?
>

My first approach is write a small plpgsql function
(based on the table definition below) like

CREATE TABLE amountlist (date TIMESTAMP,amount INTEGER);
INSERT INTO  amountlist VALUES ('2002-10-01 00:00:00', 20 ) ;
INSERT INTO  amountlist VALUES ('2002-10-02 00:00:00', 30 ) ;
INSERT INTO  amountlist VALUES ('2002-10-03 00:00:00',-15 ) ;
INSERT INTO  amountlist VALUES ('2002-10-04 00:00:00', -5 ) ;
INSERT INTO  amountlist VALUES ('2002-10-05 00:00:00', -3 ) ;
INSERT INTO  amountlist VALUES ('2002-10-06 00:00:00', 10 ) ;
CREATE FUNCTION calc_balance(TIMESTAMP) RETURNS INTEGER AS '
DECLARE balance INTEGER;
BEGIN
SELECT INTO balance SUM(amount) FROM amountlist WHERE date <= $1 ;
RETURN balance;
END;
' LANGUAGE 'plpgsql' ;

SELECT date,amount,calc_balance(date) FROM amountlist;         date          | amount | calc_balance
------------------------+--------+--------------2002-10-01 00:00:00+02 |     20 |           202002-10-02 00:00:00+02 |
  30 |           502002-10-03 00:00:00+02 |    -15 |           352002-10-04 00:00:00+02 |     -5 |
302002-10-0500:00:00+02 |     -3 |           272002-10-06 00:00:00+02 |     10 |           37
 
(6 rows)

Looks like what you are looking for, except the last value which
appears to be a typo.

Regards, Christoph