Works great.
Can you enlighten me as why the deposit is divided by the number of rows?
thanks
Ragnar wrote:
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
--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 564-0769 - Cell
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
