Thread: Incremental sum ?
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 ?
It should be done using subqueries. select ..., ( select sum(val)-sum(paid) from invoices i2 where i2.invoice_id<i.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 > >
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<i.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
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> 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;
Actually I think you could leave off the inner GROUP BY --- won't there
always be exactly one group, since only one value of inner cust_id is
selected?
regards, tom lane
On Fri, Jun 22, 2001 at 12:58:46PM -0400, Tom Lane wrote: > "Ross J. Reedstrom" <reedstrm@rice.edu> writes: > > 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; > > Actually I think you could leave off the inner GROUP BY --- won't there > always be exactly one group, since only one value of inner cust_id is > selected? Sure enough, it works fine. My internal rule: "can't use aggregates without a group by" mis-fired. Ross