Re: join problem

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