Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 99C2F4758C9
	for <pgsql-sql@postgresql.org>; Mon, 28 Oct 2002 07:27:23 -0500 (EST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id A6C2F4758E1
	for <pgsql-sql@postgresql.org>; Mon, 28 Oct 2002 07:27:22 -0500 (EST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Mon Oct 28 13:27:19 2002 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id NAA23973; Mon, 28 Oct 2002 13:27:19 +0100 (MET)
Message-ID: <3DBD2D26.AC5F676B@rodos.fzk.de>
Date: Mon, 28 Oct 2002 13:27:18 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.06 [en] (X11; I; HP-UX B.10.20 9000/780)
MIME-Version: 1.0
To: kabai@audiobox.hu
Cc: pgsql-sql@postgresql.org
Subject: Re: Accumulated sums in SQL query
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/370
X-Sequence-Number: 10087

>
> 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