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 | 20
2002-10-02 00:00:00+02 | 30 | 50
2002-10-03 00:00:00+02 | -15 | 35
2002-10-04 00:00:00+02 | -5 | 30
2002-10-05 00:00:00+02 | -3 | 27
2002-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