From: A. R. Van Hook <hook@lake-lotawana.mo.us>
To: postgresql sql list <pgsql-sql@postgresql.org>
Subject: join problem
Date: Thu, 21 Jun 2007 08:46:42 -0500
Message-ID: <467A8142.1050508@lake-lotawana.mo.us> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hook@lake-lotawana.mo.us
Subject: Re: join problem
In-Reply-To: <467A8142.1050508@lake-lotawana.mo.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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