Обсуждение: Re: Accumulated sums in SQL query
> Which is the simplest way to create an SQL query to get accumulated sums of > records like this (from the table containing the numbers): > > number sums > --------------- > 1 1 > 2 3 > 3 6 > 4 10 > SELECT number, SUM(your_sum_column) FROM your_table GROUP BY number ; Regards, Christoph
Thanks, but it seems not to be the solution to me, the example was not good enough. The table for example represents a bankaccount, records are debits and credits, and I need the balance in the new query: D&C balance --------------- 1 1 3 4 -2 2 5 7 -3 4 3 7 and so on 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 9:20 AM Subject: Re: [SQL] Accumulated sums in SQL query > > Which is the simplest way to create an SQL query to get accumulated > sums of > > records like this (from the table containing the numbers): > > > > number sums > > --------------- > > 1 1 > > 2 3 > > 3 6 > > 4 10 > > > SELECT number, SUM(your_sum_column) FROM your_table > GROUP BY number ; > > Regards, Christoph > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
>
> 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
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
>
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 *