[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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
