Thread: Query Doubt

Upto this part it is working correctly

SELECT replace(to_char(decode(lead(deptno,1,NULL) over(order by deptno) ,deptno,deptno,100)),'100','Total')deptno,
Job, Sum(sal)
FROM emp
GROUP BY rollup (deptno,job)

How to display Grand Total Message for the last row
SELECT reaplce(replace(to_char(decode(lead(deptno,1,NULL) over(order by deptno) ,deptno,deptno,100)),'100','Total'),NULL,'GrandTotal')deptno,
Job, Sum(sal)
FROM emp
GROUP BY rollup (deptno,job)

In the above query I have given null which is not correct. can anyone suggest alternate solution for that?

U better ask the same question at oracle network group.....

use this query

select decode(deptno,'Total','Total',null,'GrandTotal',deptno) deptno,job,sal from
(SELECT replace(to_char(decode(lead(deptno,1,NULL) over(order by deptno) ,deptno,deptno,100)),'100','Total') deptno,
Job, Sum(sal) sal
FROM scott.emp
GROUP BY rollup (deptno,job))