pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin problem
12+ messages / 5 participants
[nested] [flat]
* join problem
@ 2007-06-13 13:19 A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-13 13:44 ` Re: join problem Guillaume Lelarge <guillaume@lelarge.info>
2007-06-13 13:47 ` Re: join problem Ales Vojacek <alesv@fbl.cz>
2007-06-13 13:52 ` Re: join problem Ales Vojacek <alesv@fbl.cz>
2007-06-13 13:58 ` Re: join problem Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 4 replies; 12+ messages in thread
From: A. R. Van Hook @ 2007-06-13 13:19 UTC (permalink / raw)
To: pgsql-sql
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"
Where do I add the cai table reference???
thanks
--
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-13 13:44 ` Guillaume Lelarge <guillaume@lelarge.info>
3 siblings, 0 replies; 12+ messages in thread
From: Guillaume Lelarge @ 2007-06-13 13:44 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
A. R. Van Hook a écrit :
> 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"
>
> Where do I add the cai table reference???
>
You probably need to change the order of the tables in the FROM clause.
Replace
FROM invoice v, cai
with
FROM cai, invoice v
Regards.
--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://docs.postgresqlfr.org/ -->
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-13 13:47 ` Ales Vojacek <alesv@fbl.cz>
3 siblings, 0 replies; 12+ messages in thread
From: Ales Vojacek @ 2007-06-13 13:47 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
There is not referenced table cai in from clausule.
A. R. Van Hook napsal(a):
> 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"
>
> Where do I add the cai table reference???
>
> thanks
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-13 13:52 ` Ales Vojacek <alesv@fbl.cz>
3 siblings, 0 replies; 12+ messages in thread
From: Ales Vojacek @ 2007-06-13 13:52 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
You can do it like this:
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
join cai on v.cusid = cai.cusid
left outer join
invoiceitems i
on v.ivid = i.ivid
where v.cusid = $cusid
group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db
A.
Aleš Vojáček
FBL Group spol. s r.o.
e-mail: alesv@fbl.cz
mobil: +420603893335
A. R. Van Hook napsal(a):
> 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"
>
> Where do I add the cai table reference???
>
> thanks
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-13 13:58 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-19 21:07 ` Re: join problem Michael Glaesemann <grzm@seespotcode.net>
3 siblings, 1 reply; 12+ messages in thread
From: Michael Glaesemann @ 2007-06-13 13:58 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-13 13:58 ` Re: join problem Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-19 21:07 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 12+ messages in thread
From: Michael Glaesemann @ 2007-06-19 21:07 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
[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
^ permalink raw reply [nested|flat] 12+ messages in thread
* join problem
@ 2007-06-21 13:46 A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Re: join problem Ragnar <gnari@hive.is>
0 siblings, 1 reply; 12+ messages in thread
From: A. R. Van Hook @ 2007-06-21 13:46 UTC (permalink / raw)
To: pgsql-sql
I have three tables relating to purchases
invoice - transaction data (customer id, deposit. etc)
invoiceitems - purachace items detail
cai - customer data
if I query for the total charges using
select sum(rowtot + tax)
from invoiceitems
where ivid in (select ivid from invoice where cusid = 2128)"
I get 1179.24 which is correct.
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
Can someone correct the query?
thanks
tables definations are as follows:
invoice
(ivid int NOT NULL PRIMARY KEY,
rid int null references registry,
sid int not null references staffname,
cusid int,
invdate date,
ifname varchar(16),
imi char,
ilname varchar(16),
addr text,
city varchar(16),
state varchar(2),
zip varchar(16),
iphone varchar(16),
eventdate date,
paytype int,
bust varchar(16),
height varchar(16),
dressize varchar(16),
waist varchar(16),
hips varchar(16),
hollow varchar(16),
deposit numeric(6,2),
transtype int,
notes text,
neck varchar(16),
arm_length varchar(16),
leg_length varchar(16),
coat varchar(16),
shoe varchar(16),
tux int default 0
invoiceItems
(item int NOT NULL,
ivid int NOT NULL references invoice ON DELETE
CASCADE,
qty int,
stid int references stock, /*tag*/
descript text,
price numeric(6,2),
tax numeric(7,3),
discount numeric(6,2),
rowtot numeric(7,3),
pickup int default 0, /* SO or to be picked up =
1 */
primary key (item, ivid)
create table cai /* customer account information*/
(cusid int NOT null primary key,
cfname varchar(16),
cmi char default '',
clname varchar(16),
caddr text,
ccity varchar(16),
cstate varchar(2),
czip varchar(16),
cphone varchar(16),
db numeric(7,2),
tcode int not null default 0,
acode int not null default 0,
tdate timestamp not null
[hook@f6 ~]$
--
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-21 18:43 ` Ragnar <gnari@hive.is>
2007-06-23 09:15 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
0 siblings, 1 reply; 12+ messages in thread
From: Ragnar @ 2007-06-21 18:43 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Re: join problem Ragnar <gnari@hive.is>
@ 2007-06-23 09:15 ` A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-23 12:46 ` Re: join problem Ragnar <gnari@hive.is>
0 siblings, 1 reply; 12+ messages in thread
From: A. R. Van Hook @ 2007-06-23 09:15 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; +Cc: pgsql-sql
What is the correct query???
thanks
Ragnar wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Re: join problem Ragnar <gnari@hive.is>
2007-06-23 09:15 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-23 12:46 ` Ragnar <gnari@hive.is>
2007-06-24 11:14 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
0 siblings, 1 reply; 12+ messages in thread
From: Ragnar @ 2007-06-23 12:46 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Re: join problem Ragnar <gnari@hive.is>
2007-06-23 09:15 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-23 12:46 ` Re: join problem Ragnar <gnari@hive.is>
@ 2007-06-24 11:14 ` A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-24 12:10 ` Re: join problem Ragnar <gnari@hive.is>
0 siblings, 1 reply; 12+ messages in thread
From: A. R. Van Hook @ 2007-06-24 11:14 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: join problem
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Re: join problem Ragnar <gnari@hive.is>
2007-06-23 09:15 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-23 12:46 ` Re: join problem Ragnar <gnari@hive.is>
2007-06-24 11:14 ` Re: join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2007-06-24 12:10 ` Ragnar <gnari@hive.is>
0 siblings, 0 replies; 12+ messages in thread
From: Ragnar @ 2007-06-24 12:10 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2007-06-24 12:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-13 13:19 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-13 13:44 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-06-13 13:47 ` Ales Vojacek <alesv@fbl.cz>
2007-06-13 13:52 ` Ales Vojacek <alesv@fbl.cz>
2007-06-13 13:58 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-19 21:07 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-21 13:46 join problem A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-21 18:43 ` Ragnar <gnari@hive.is>
2007-06-23 09:15 ` A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-23 12:46 ` Ragnar <gnari@hive.is>
2007-06-24 11:14 ` A. R. Van Hook <hook@lake-lotawana.mo.us>
2007-06-24 12:10 ` Ragnar <gnari@hive.is>
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