From: Michael Glaesemann <grzm@seespotcode.net>
To: A. R. Van Hook <hook@lake-lotawana.mo.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: join problem
Date: Tue, 19 Jun 2007 16:07:58 -0500
Message-ID: <50AD6CA6-3D28-457E-BCF9-568E0B4DD410@seespotcode.net> (raw)
In-Reply-To: <46782BC8.1050005@lake-lotawana.mo.us>
References: <466FEEC9.4050300@lake-lotawana.mo.us>
<D6358648-EFC8-489A-8F07-450CA5B85795@seespotcode.net>
<46782BC8.1050005@lake-lotawana.mo.us>
[Please don't top post as it makes the discussion more difficult to
follow, and please reply to the list so that others may benefit from
and participate in the discussion.]
On Jun 19, 2007, at 14:17 , A. R. Van Hook wrote:
> Michael Glaesemann wrote:
>>
>> On Jun 13, 2007, at 8:19 , A. R. Van Hook wrote:
>>
>>> I have join problem:
>>> "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db,
>>> sum(i.tax) as tax,
>>> sum(i.tax + i.rowtot) as totalP,
>>> (sum(i.tax + i.rowtot) - v.deposit) as balance
>>> from invoice v
>>> left outer join
>>> invoiceitems i
>>> on v.ivid = i.ivid
>>> where v.cusid = $cusid
>>> and v.cusid = cai.cusid
>>> group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db
>>> ERROR: missing FROM-clause entry for table "cai"
>>>
>>> If I add cai to the from clause "from invoice v, cai, I get
>>> ERROR: missing FROM-clause entry for table "cai"
>>> ERROR: invalid reference to FROM-clause entry for table "v"
>>
>> I think you may need to change the order of the JOIN clause. Does
>> this work?
>>
>> SELECT i.ivid
>> , v.eventdate
>> , v.deposit
>> , v.invdate
>> , cai.db
>> , sum(i.tax) as tax
>> , sum(i.tax + i.rowtot) as totalP
>> , (sum(i.tax + i.rowtot) - v.deposit) as balance
>> FROM cai
>> JOIN invoice v ON (cai.cusid = v.cusid)
>> LEFT JOIN invoiceitems i ON (v.ivid = i.ivid)
>> WHERE v.cusid = $cusid
>> GROUP BY i.ivid
>> , v.eventdate
>> , v.deposit
>> , v.invdate
>> , cai.db
>>
>> Note I've also moved the cai.cusid = v.cusid into the JOIN
>> condition (which is what it is). Also, if cai doesn't have a ivid
>> column and invoiceitems doesn't have a cusid column, you can use
>> USING (cusid) and USING (ivid) rather than ON (cai.cusid =
>> v.cusid) and ON (v.ivid = i.ivid), which has the nice property of
>> outputing only one join column rather than one column for each
>> table, (i.e., only one cusid column rather than one each for cai
>> and invoice).
>>
>> Michael Glaesemann
>> grzm seespotcode net
> This solution works fine but the summations are reporting
> individual row data.
> i.e.
> ivid | eventdate | deposit | invdate | db | tax | totalp
> | balance
> ------+------------+---------+------------+------+--------+---------
> +---------
> 7610 | 10/15/2005 | 0.00 | 05/05/2005 | 0.00 | 11.490 | 170.490
> | 170.490
> 7868 | 10/15/2005 | 85.25 | 06/04/2005 | 0.00 | |
> | 8620 | 10/15/2005 | 85.24 | 09/07/2005 | 0.00 | 0.000
> | 0.000 | -85.240
>
> can the query be modified to get the overall totals of each
> (db,tax,totalp,balance)?
If you want totals for db, tax, totalp, and balance, you'll need to
modify the rows that are returned (the SELECT list) and the GROUP BY
clause to group those together. I don't know what you want to the
totals over: eventdate? ivid? Give it a try and if you still have
questions, be sure to post what you've attempted.
Michael Glaesemann
grzm seespotcode net
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: grzm@seespotcode.net, hook@lake-lotawana.mo.us
Subject: Re: join problem
In-Reply-To: <50AD6CA6-3D28-457E-BCF9-568E0B4DD410@seespotcode.net>
* 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