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
[EMAIL PROTECTED] ~]$
--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
