Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 85F759FB452
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 23 Jun 2007 09:31:08 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 05855-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 23 Jun 2007 09:30:58 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164])
	by postgresql.org (Postfix) with ESMTP id 2EF6A9FB43B
	for <pgsql-sql@postgresql.org>; Sat, 23 Jun 2007 09:31:02 -0300 (ADT)
Received: (qmail 25104 invoked by uid 1010); 23 Jun 2007 12:30:43 +0000
Received: from 88.149.106.19 by mack (envelope-from <gnari@hive.is>,
	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.307169 secs); 23 Jun 2007 12:30:43 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 0.307169 secs Process 25097)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx1.ipf.is with SMTP; 23 Jun 2007 12:30:42 +0000
Subject: Re: join problem
From: Ragnar <gnari@hive.is>
To: "A. R. Van Hook" <hook@lake-lotawana.mo.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
In-Reply-To: <467CE499.4020400@lake-lotawana.mo.us>
References: <467A8142.1050508@lake-lotawana.mo.us>
	<1182451380.5953.232.camel@localhost.localdomain>
	<467CE499.4020400@lake-lotawana.mo.us>
Content-Type: text/plain
Date: Sat, 23 Jun 2007 12:46:22 +0000
Message-Id: <1182602782.5953.237.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/185
X-Sequence-Number: 28675

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