Thread: join problem
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
invoicewhere 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)
wherev.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 - RetiredThe City of Lake Lotawana
hook@lake-lotawana.mo.us
hook@lota.us
avanhook3@comcast.net
(816) 578-4704 - Home
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
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 - RetiredThe City of Lake Lotawana hook@lake-lotawana.mo.us hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 564-0769 - Cell
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
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 - RetiredThe City of Lake Lotawana hook@lake-lotawana.mo.us hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 564-0769 - Cell
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