SELECT tid,
to_char(tdate,'mm/dd/yy') tdate,
cnbr,
pnbr,
to_char(cdate,'hh24:mi:ss') ctime,
pid,
term,
ttype,
dollaramount,
lrate,
status,
cname,
to_char(sdate,'mm/dd/yy') sdate,
to_char(mdate,'mm/dd/yy') mdate,
aloginname,
decode('10118',pnbr,
decode(ttype,'S',

(SELECT firstname||' '||lastname
FROM member_m WHERE lname=cname),'B',

(SELECT firstname||' '||lastname FROM member_m
WHERE lname=aloginname)),
cnbr,decode(ttype,'S',

(SELECT firstname||' '||lastname
from member_m a, insmktrate_d b
WHERE a.lname=nvl(b.modby,b.createby) AND b.gid=insgid),'B',
(SELECT firstname||' '||lastname
FROM member_m WHERE loginname=cname))) name

FROM trading_t

WHERE '10118' in (cnbr,pnbr)
AND insidemrkt='Y'
AND TRIM(Tdate)
between to_date('12/18/2002','mm/dd/yyyy')
and to_date('12/18/2002','mm/dd/yyyy')
AND ('Q'='Q' or status='Q')
AND ('ALL'='ALL' or pId='ALL')
AND aloginname in (
SELECT lname
FROM member_m
WHERE catid ='9'
AND active ='Y')


ORDER BY term,
cnbr,
pnbr



with the above qry iam getting the as follows but what i require along with the information i want group by sub totals tobe
displayed for dollar amount that i tried using roll up but it is not giving the out put correctly as i haveto group every column
but i need to group and get total only on clname.that is for 'xxx' the sub total is to be displayed as a seperate row
is it possible through qry if so plz.. tell me how







TID TDATe CNbr Pnbr Titem PROD TERM T DOLLARAMOUNT LRATE Status CNAME Sdate Mdate aloGINNAME
-------------------- -------- ----- ----- -------- ---- ---------- - ------------ ---------- - -------------------- -------- -------- -------------------- -------------------------------------------------------
12182002000002 12/18/02 10005 10118 10:14:05 1000 O/N S 1200000000 1100 D xxx 12/18/02 12/19/02
12182002000003 12/18/02 10005 10118 10:15:38 1000 O/N S 1000000000 1 D xxx 12/18/02 12/19/02
12182002000005 12/18/02 10118 10502 13:09:42 1000 O/N S 1000000000 2.3 D zzz 12/18/02 12/19/02
12182002000004 12/18/02 10118 10512 13:09:01 1000 O/N S 1800000000 1.3 D zzz 12/18/02 12/19/02


THAT IS IT SHOULD DISPLAY AS FOLLOWS:WITH out using sql*plus reporting commands

12182002000002 12/18/02 10005 10118 10:14:05 1000 O/N S 1200000000 1100 D xxx 12/18/02 12/19/02
12182002000003 12/18/02 10005 10118 10:15:38 1000 O/N S 1000000000 1 D xxx 12/18/02 12/19/02

220000000

12182002000005 12/18/02 10118 10502 13:09:42 1000 O/N S 1000000000 2.3 D zzz 12/18/02 12/19/02
12182002000004 12/18/02 10118 10512 13:09:01 1000 O/N S 1800000000 1.3 D zzz 12/18/02 12/19/02