From: Ragnar <gnari@hive.is>
To: A. R. Van Hook <hook@lake-lotawana.mo.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: join problem
Date: Sat, 23 Jun 2007 12:46:22 +0000
Message-ID: <1182602782.5953.237.camel@localhost.localdomain> (raw)
In-Reply-To: <467CE499.4020400@lake-lotawana.mo.us>
References: <467A8142.1050508@lake-lotawana.mo.us>
<1182451380.5953.232.camel@localhost.localdomain>
<467CE499.4020400@lake-lotawana.mo.us>
On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote:
> Ragnar wrote:
> > On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote:
> >> If I try an inclusive query using the following:
> >> select
> >> sum(i.rowtot + i.tax) as tot,
> >> sum(v.deposit) as deposit
> >> from cai c
> >> join invoice v on (v.cusid = c.cusid)
> >> left join invoiceitems i on (v.ivid = i.ivid)
> >> where v.cusid = 2128
> >> group by
> >> c.cusid
> >> I get
> >> tot | deposit
> >> ----------+---------
> >> 1179.240 | 2819.24
> >
> > you are adding the invoice deposit once for each item
> >
> What is the correct query???
sum each invoice separately, and then group the sums by cusid.
for example:
select vcusid as cusid,
sum(vtot) as tot,
sum(vdeposit) as deposit
from (
select
v.cusid as vcusid,
v.ivid as vivid,
sum(i.rowtot + i.tax) as vtot,
sum(v.deposit)/count(*) as vdeposit
from invoice as v
left join invoiceitems as i on (v.ivid = i.ivid)
group by v.cusid,
v.ivid
) as vsums
where vsums.vcusid=2128
group by vsums.vcusid
hope this helps
gnari
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: gnari@hive.is, hook@lake-lotawana.mo.us
Subject: Re: join problem
In-Reply-To: <1182602782.5953.237.camel@localhost.localdomain>
* 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