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 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 - RetiredThe City of Lake Lotawana

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