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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org