pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIncremental sum ?
5+ messages / 4 participants
[nested] [flat]
* Incremental sum ?
@ 2001-06-22 08:16 Domingo Alvarez Duarte <domingo@dad-it.com>
0 siblings, 1 reply; 5+ messages in thread
From: domingo@dad-it.com @ 2001-06-22 08:16 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incremental sum ?
@ 2001-06-22 15:29 Alex Pilosov <alex@pilosoft.com>
parent: Domingo Alvarez Duarte <domingo@dad-it.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alex Pilosov @ 2001-06-22 15:29 UTC (permalink / raw)
To: Domingo Alvarez Duarte <domingo@dad-it.com>; +Cc: pgsql-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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incremental sum ?
@ 2001-06-22 16:21 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Alex Pilosov <alex@pilosoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ross J. Reedstrom @ 2001-06-22 16:21 UTC (permalink / raw)
To: Alex Pilosov <alex@pilosoft.com>; +Cc: Domingo Alvarez Duarte <domingo@dad-it.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incremental sum ?
@ 2001-06-22 16:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-06-22 16:58 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: Alex Pilosov <alex@pilosoft.com>; Domingo Alvarez Duarte <domingo@dad-it.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incremental sum ?
@ 2001-06-22 17:18 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Ross J. Reedstrom @ 2001-06-22 17:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alex Pilosov <alex@pilosoft.com>; Domingo Alvarez Duarte <domingo@dad-it.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-06-22 17:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-22 08:16 Incremental sum ? Domingo Alvarez Duarte <domingo@dad-it.com>
2001-06-22 15:29 ` Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:21 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-06-22 16:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-22 17:18 ` Ross J. Reedstrom <reedstrm@rice.edu>
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