Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 285D59FB60E for ; Sun, 24 Jun 2007 08:54:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 48682-02 for ; Sun, 24 Jun 2007 08:54:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx2.ipf.is (jeep.ipf.is [85.197.192.148]) by postgresql.org (Postfix) with ESMTP id 1D2099FB619 for ; Sun, 24 Jun 2007 08:54:41 -0300 (ADT) Received: (qmail 23131 invoked by uid 1010); 24 Jun 2007 11:54:35 +0000 Received: from 88.149.106.19 by jeep (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.282758 secs); 24 Jun 2007 11:54:35 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via jeep X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.282758 secs Process 23124) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx2.ipf.is with SMTP; 24 Jun 2007 11:54:34 +0000 Subject: Re: join problem From: Ragnar To: "A. R. Van Hook" Cc: postgresql sql list In-Reply-To: <467E51F8.80302@lake-lotawana.mo.us> 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> <467E51F8.80302@lake-lotawana.mo.us> Content-Type: text/plain Date: Sun, 24 Jun 2007 12:10:15 +0000 Message-Id: <1182687015.5953.270.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/189 X-Sequence-Number: 28679 On sun, 2007-06-24 at 06:14 -0500, A. R. Van Hook wrote: [ in the future, please avoid top-posting, as it is annoying to have to rearrange lines when replying ] > 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: > >>> > >>>> [problem involving a join, with sum on base table column] > >>> 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: > > ... > > 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 > > ... > Works great. > Can you enlighten me as why the deposit is divided by the number of rows? maybe an example would clarify a bit: say you have only one invoice (deposit=100), with 2 invoiceitems (both with rowtot=50) "select sum(deposit) from invoice" returns 100 "select sum(rowtot) from invoiceitems" returns 100 the query: select ivid,deposit,rowtot from invoice left join invoiceitems on (invoice.ivid = invoiceitems.ivid) returns the rows: ivid | deposit | rowtot 1 | 100 | 50 1 | 100 | 50 if you just select a SUM(deposit) on that join, you get 200, not 100 because the value is repeated for each invoiceitem. hope this helps gnari