Re: join problem

What is the correct query???

thanks

Ragnar wrote:
> On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote:
>
>> if I query for the total deposit using
>> select sum(deposit)
>> from invoice
>> where cusid = 2128"
>>
>> I also get 1179.24, also the correct amount
>>
>>
>> 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
>
> gnari
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana

hook(at)lake-lotawana(dot)mo(dot)us
hook(at)lota(dot)us
avanhook3(at)comcast(dot)net
(816) 578-4704 - Home
(816) 564-0769 - Cell