Thread: 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 *