Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 44A5B9FB7F5 for ; Sun, 24 Jun 2007 08:19:22 -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 87039-05 for ; Sun, 24 Jun 2007 08:19:17 -0300 (ADT) X-Greylist: delayed 00:05:01.694223 by SQLgrey-1.7.5 Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.154]) by postgresql.org (Postfix) with ESMTP id 630A19FB781 for ; Sun, 24 Jun 2007 08:19:17 -0300 (ADT) Received: from [192.168.1.6] (c-69-242-166-153.hsd1.mo.comcast.net[69.242.166.153]) by comcast.net (rwcrmhc14) with ESMTP id <20070624111402m14007sa56e>; Sun, 24 Jun 2007 11:14:13 +0000 Message-ID: <467E51F8.80302@lake-lotawana.mo.us> Date: Sun, 24 Jun 2007 06:14:00 -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> <467CE499.4020400@lake-lotawana.mo.us> <1182602782.5953.237.camel@localhost.localdomain> In-Reply-To: <1182602782.5953.237.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/188 X-Sequence-Number: 28678 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 hook@lake-lotawana.mo.us hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 564-0769 - Cell