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

I think,that is not good solution. What will happen when some records
have the same date? I suggest to operate on rowid or something like
thaţ (maybe some serial?) and everything will be good.
But, when each row has different date, its good :)
regards.
marek

On Mon, Oct 28, 2002 at 03:55:46PM +0100, Kabai J?zsef (I don't know why;) wrote:
: 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
: > 
: 
: 
: 
: ---------------------------(end of broadcast)---------------------------
: TIP 2: you can get off all lists at once with the unregister command
:     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Marek Bartnikowski       http://easy.eu.org* It is more complicated than you think *