8,127
Displaying the total number of employees department wise.
=============================================
Expand|Select|Wrap|Line Numbers
- select deptno,count(deptno) from emp group by deptno;
=====================
Expand|Select|Wrap|Line Numbers
- select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
- sum(case when deptno=20 then 1 else 0 end) as deptno_20,
- sum(case when deptno=30 then 1 else 0 end) as deptno_30,
- sum(case when deptno=40 then 1 else 0 end) as deptno_40
- from emp;
======================================
Expand|Select|Wrap|Line Numbers
- select max(case when job='CLERK'
- then ename else null end) as clerks,
- max(case when job='ANALYST'
- then ename else null end) as analysts,
- max(case when job='MANAGER'
- then ename else null end) as mgrs,
- max(case when job='PRESIDENT'
- then ename else null end) as presi,
- max(case when job='SALESMAN'
- then ename else null end) as sales
- from (
- select job,
- ename,
- row_number()over(partition by job order by ename) rn
- from emp
- ) x
- group by rn
=============
Expand|Select|Wrap|Line Numbers
- select dept.deptno,
- case dept.deptno
- when 10 then emp_cnts.deptno_10
- when 20 then emp_cnts.deptno_20
- when 30 then emp_cnts.deptno_30
- end as counts_by_dept
- from (
- Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
- sum(case when deptno=20 then 1 else 0 end) as deptno_20,
- sum(case when deptno=30 then 1 else 0 end) as deptno_30
- from emp
- ) emp_cnts,
- (select deptno from dept where deptno <= 30) dept
Display in single column
=====================
Expand|Select|Wrap|Line Numbers
- select case rn
- when 1 then ename
- when 2 then job
- when 3 then cast(sal as char(4))
- end emps
- from (
- select e.ename,e.job,e.sal,
- row_number()over(partition by e.empno
- order by e.empno) rn
- from emp e,
- (select *
- from emp where job='CLERK') four_rows
- where e.deptno=10
- ) x
========================================
Expand|Select|Wrap|Line Numbers
- select to_number(
- decode(lag(deptno)over(order by deptno),
- deptno,null,deptno)
- ) deptno, ename
- from emp
================================================== ==
Expand|Select|Wrap|Line Numbers
- select d20_sal - d10_sal as d20_10_diff,
- d20_sal - d30_sal as d20_30_diff
- from (
- select sum(case when deptno=10 then sal end) as d10_sal,
- sum(case when deptno=20 then sal end) as d20_sal,
- sum(case when deptno=30 then sal end) as d30_sal
- from emp
- ) totals_by_dept
