join problem

From: "A(dot) R(dot) Van Hook" <hook(at)lake-lotawana(dot)mo(dot)us>
To: postgresql sql list <pgsql-sql(at)postgresql(dot)org>
Subject: join problem
Date: 2007-06-21 13:46:42
Message-ID: 467A8142.1050508@lake-lotawana.mo.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)f6 ~]$

--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana

hook(at)lake-lotawana(dot)mo(dot)us
hook(at)lota(dot)us
avanhook3(at)comcast(dot)net
(816) 578-4704 - Home

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Gardner 2007-06-21 13:47:53 Re: Select last there dates
Previous Message Loredana Curugiu 2007-06-21 13:35:37 Re: [SQL] Select last there dates