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
