Re: [SQL] join problem

[A. R. Van Hook]

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

[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