From: Ragnar <gnari@hive.is>
To: A. R. Van Hook <hook@lake-lotawana.mo.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: join problem
Date: Sun, 24 Jun 2007 12:10:15 +0000
Message-ID: <1182687015.5953.270.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@hive.is, hook@lake-lotawana.mo.us
Subject: Re: join problem
In-Reply-To: <1182687015.5953.270.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox