 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,569
# 1
Sep 17 '07
| |
Displaying Histogram-Horizontal
=========================== - select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
=============== - select row_number( )over(partition by deptno order by empno) rn,
-
case when deptno=10 then '*' else null end deptno_10,
-
case when deptno=20 then '*' else null end deptno_20,
-
case when deptno=30 then '*' else null end deptno_30
-
from emp
Displaying only the histogram
============================ - select max(deptno_10) d10,
-
max(deptno_20) d20,
-
max(deptno_30) d30
-
from (
-
select row_number( )over(partition by deptno order by empno) rn,
-
case when deptno=10 then '*' else null end deptno_10,
-
case when deptno=20 then '*' else null end deptno_20,
-
case when deptno=30 then '*' else null end deptno_30
-
from emp
-
) x
-
group by rn
-
order by 1 desc, 2 desc, 3 desc
Returning Non-GROUP BY Columns
============================ - select deptno,ename,job,sal,
-
case when sal = max_by_dept
-
then 'TOP SAL IN DEPT'
-
when sal = min_by_dept
-
then 'LOW SAL IN DEPT'
-
end dept_status,
-
case when sal = max_by_job
-
then 'TOP SAL IN JOB'
-
when sal = min_by_job
-
then 'LOW SAL IN JOB'
-
end job_status
-
from (
-
select deptno,ename,job,sal,
-
max(sal)over(partition by deptno) max_by_dept,
-
max(sal)over(partition by job) max_by_job,
-
min(sal)over(partition by deptno) min_by_dept,
-
min(sal)over(partition by job) min_by_job
-
from emp
-
) emp_sals
-
where sal in (max_by_dept,max_by_job,
-
min_by_dept,min_by_job)
Using ROLLUP to display total
============================= - select case grouping(job)
-
when 0 then job
-
else 'TOTAL'
-
end job,
-
sum(sal) sal
-
from emp
-
group by rollup(job)
ROLLUP (10g):- TO GENERATE TOTALS AND SUB-TOTALS IN THE SUMMERISED RESULT.IT CAN ONLY APPEAR IN A QUERY WITH A GROUP BY CLAUSE. - select empno,sum(sal) from emp group by rollup(empno);
-
-
select empno,ename,sum(sal) from emp group by rollup(empno,ename);
-
-
select empno,ename,sum(sal) from emp group by rollup(ename,empno);
-
-
select empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
-
-
select grouping(empno),empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
Also check Pivoting - 3 |
