Re: Accumulated sums in SQL query - Mailing list pgsql-sql

Thank you Christoph this logic helped me a lot.
Regards Joseph
----- Original Message -----
From: "Christoph Haller" <ch@rodos.fzk.de>
To: <kabai@audiobox.hu>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, October 28, 2002 1:27 PM
Subject: Re: [SQL] 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>