Query help

[irehman]

Using EMP Table
SQL> select deptno, sum(sal) from emp
2 group by deptno;

DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400

Now How do I get % of total

DEPTNO SUM(SAL) %
---------- ---------- -----
10 8750 30.14
20 10875 37.46
30 9400 32.38

I want another column with 8750/29025 and so on(which is a grand total) I like to get % of sal at each deptno level.

Thanks in Advance

[balajiyes]

try this...

create table test(deptno number, sal number);
insert into test values(10, 8750);
insert into test values(20, 10875);
insert into test values(30, 9400);
commit;

select deptno, sal, sum(sal) over (), sal / sum(sal) over () final_val from test;

Cheers!

[balajiyes]

select deptno, sal, (sal / sum(sal) over () * 100) final_val from test
group by deptno, sal;

[irehman]

Is it possible to do it, without craeting new table, some how within the query?

[balajiyes]

Is it possible to do it, without craeting new table, some how within the query? -- i don't understand this...


since i don't hv EMP table as in ur example I create one called TEST.

Cheers!

[bkoniki]

select t1.sub_t_sal*100/t2.grant_t_sal sal_per
from
(select deptno, sum(sal) sub_t_sal
from emp
group by deptno
) t1,
(select sum(sal) grand_t_sal
from emp
) t2


since pseudo table t2 is having one row, we need not worry about cartesian product.


Good luck !!

[Janes]

select a.deptno,sum(a.sal),a.sal/b.totalsal*100 || '%' from test a,(select sum(sal) totalsal from test) b group by a.deptno,a.sal/b.totalsal