Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E58479FB43D for ; Sat, 23 Jun 2007 06:25:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59042-10 for ; Sat, 23 Jun 2007 06:25:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82]) by postgresql.org (Postfix) with ESMTP id E94FE9FB424 for ; Sat, 23 Jun 2007 06:25:15 -0300 (ADT) Received: from [192.168.1.6] (c-69-242-166-153.hsd1.mo.comcast.net[69.242.166.153]) by comcast.net (rwcrmhc12) with ESMTP id <20070623091521m12008jl78e>; Sat, 23 Jun 2007 09:15:22 +0000 Message-ID: <467CE499.4020400@lake-lotawana.mo.us> Date: Sat, 23 Jun 2007 04:15:05 -0500 From: "A. R. Van Hook" User-Agent: Thunderbird 1.5.0.12 (X11/20070530) MIME-Version: 1.0 To: Ragnar CC: postgresql sql list Subject: Re: join problem References: <467A8142.1050508@lake-lotawana.mo.us> <1182451380.5953.232.camel@localhost.localdomain> In-Reply-To: <1182451380.5953.232.camel@localhost.localdomain> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/182 X-Sequence-Number: 28672 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@lake-lotawana.mo.us hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 564-0769 - Cell