From: Christoph Haller <ch@rodos.fzk.de>
To: kabai@audiobox.hu
Cc: pgsql-sql@postgresql.org
Subject: Re: Accumulated sums in SQL query
Date: Mon, 28 Oct 2002 13:27:18 +0100
Message-ID: <3DBD2D26.AC5F676B@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, kabai@audiobox.hu
Subject: Re: Accumulated sums in SQL query
In-Reply-To: <3DBD2D26.AC5F676B@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox