Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5MGLba64115 for ; Fri, 22 Jun 2001 12:21:37 -0400 (EDT) (envelope-from reedstrm@rice.edu) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id D216168A0E; Fri, 22 Jun 2001 11:21:36 -0500 (CDT) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 15DTgO-0003ds-00; Fri, 22 Jun 2001 11:21:20 -0500 Date: Fri, 22 Jun 2001 11:21:19 -0500 From: "Ross J. Reedstrom" To: Alex Pilosov Cc: Domingo Alvarez Duarte , pgsql-sql@postgresql.org Subject: Re: Incremental sum ? Message-ID: <20010622112119.D13451@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Alex Pilosov , Domingo Alvarez Duarte , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.18i X-Archive-Number: 200106/377 X-Sequence-Number: 3669 And here's the working example: not the need to GROUP BY, and <= to get the current payment. select cust_id,invoice_id,val,paid, (select (sum(val) - sum(paid)) from invoices_not_paid where cust_id= i.cust_id and invoice_id <= i.invoice_id group by cust_id) as balance from invoices_not_paid i; and it's output: cust_id | invoice_id | val | paid | balance ---------+------------+-----------+----------+----------- 1 | 23 | 10.500000 | 3.400000 | 7.100000 1 | 34 | 5.700000 | 0.000000 | 12.800000 1 | 67 | 23.890000 | 4.500000 | 32.190000 (3 rows) Ross On Fri, Jun 22, 2001 at 11:29:25AM -0400, Alex Pilosov wrote: > It should be done using subqueries. > select ..., ( > select sum(val)-sum(paid) from invoices i2 > where i2.invoice_id and i2.cust_id=i.cust_id > ) > from invoices i > > > On 22 Jun 2001, Domingo Alvarez Duarte wrote: > > > I have a problem that requires what I call a incremental sum, lets say > > I have the folowing table (for simplicity): > > > > table invoices_not_paid(cust_id int, invoice_id int, val numeric, paid > > numeric); > > > > with the folowing values: > > > > cust_id invoice_id val paid > > ---------------------------------- > > 1 23 10.50 3.40 > > 1 34 5.70 0.0 > > 1 67 23.89 4.50 > > > > > > I want show a list like this: > > > > cust_id invoice_id val paid incremental_not_paid_sum > > ----------------------------------------------------------------- > > 1 23 10.50 3.40 (10.50 - 3.40) 7.10 > > 1 34 5.70 0.0 (7.10 + 5.70 - 0.0) 12.80 > > 1 67 23.89 4.50 (12.80 + 23.89 - 4.50) 31.19 > > > > The operations betwen () are showed only to explain how the > > incremental_not_paid_sum is calculated, The operation requires a > > reference to a previous column or a partial sum of columns till that > > moment, someone has an idea how this can be done using sql ? > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl