Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 7CF949FB6DF
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 15:28:07 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 73260-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 15:28:03 -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 D57AE9FB4F5
	for <pgsql-sql@postgresql.org>; Thu, 21 Jun 2007 15:28:02 -0300 (ADT)
Received: (qmail 24318 invoked by uid 1010); 21 Jun 2007 18:27:58 +0000
Received: from 88.149.106.19 by jeep (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.289774 secs); 21 Jun 2007 18:27:58 -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.289774 secs Process 24311)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx2.ipf.is with SMTP; 21 Jun 2007 18:27:58 +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: <467A8142.1050508@lake-lotawana.mo.us>
References: <467A8142.1050508@lake-lotawana.mo.us>
Content-Type: text/plain
Date: Thu, 21 Jun 2007 18:43:00 +0000
Message-Id: <1182451380.5953.232.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/170
X-Sequence-Number: 28660

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