Re: join problem

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 - RetiredThe City of Lake Lotawana

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