From: A. R. Van Hook <hook@lake-lotawana.mo.us>
To: Ragnar <gnari@hive.is>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: join problem
Date: Sun, 24 Jun 2007 06:14:00 -0500
Message-ID: <467E51F8.80302@lake-lotawana.mo.us> (raw)
In-Reply-To: <1182602782.5953.237.camel@localhost.localdomain>
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>
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
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: hook@lake-lotawana.mo.us, gnari@hive.is
Subject: Re: join problem
In-Reply-To: <467E51F8.80302@lake-lotawana.mo.us>
* 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