Re: Accumulated sums in SQL query
От | Kabai József |
---|---|
Тема | Re: Accumulated sums in SQL query |
Дата | |
Msg-id | 007201c27e92$19a78850$6b01a8c0@audiobox.hu обсуждение исходный текст |
Ответ на | Re: Accumulated sums in SQL query (Christoph Haller <ch@rodos.fzk.de>) |
Ответы |
Re: Accumulated sums in SQL query
(Marek Bartnikowski <marek@easy.eu.org>)
|
Список | 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 >
В списке pgsql-sql по дате отправления: