join problem - Mailing list 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
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