# Calculate Median

• Script Name Calculate Median
• Description Median Calculation
• Category SQL Analytics
• Contributor Functions
• Created Monday October 30, 2017
• Statement 1
``````SELECT EMPNO, DEPTNO, SAL , PERCENTILE_DISC(0.5)

WITHIN GROUP (ORDER BY SAL DESC)

OVER (PARTITION BY DEPTNO) "Percentile_DISC",percentile_cont(0.5) within group (order by sal desc) OVER (PARTITION BY DEPTNO) "median cont"

FROM EMP``````
ORA-00942: table or view does not exist
• Statement 2
Create DEPT table which will be the parent table of the EMP table.
``````create table dept(
deptno     number(2,0),
dname      varchar2(14),
loc        varchar2(13),
constraint pk_dept primary key (deptno)
)``````
Table created.
• Statement 3
Create the EMP table which has a foreign key reference to the DEPT table. The foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO column in the DEPT table.
``````create table emp(
empno    number(4,0),
ename    varchar2(10),
job      varchar2(9),
mgr      number(4,0),
hiredate date,
sal      number(7,2),
comm     number(7,2),
deptno   number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)``````
Table created.
• Statement 4
Insert row into DEPT table using named columns.
``````insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK')``````
1 row(s) inserted.
• Statement 5
Insert a row into DEPT table by column position.
``````insert into dept
values(20, 'RESEARCH', 'DALLAS')``````
1 row(s) inserted.
• Statement 6
``````insert into dept
values(30, 'SALES', 'CHICAGO')``````
1 row(s) inserted.
• Statement 7
``````insert into dept
values(40, 'OPERATIONS', 'BOSTON')``````
1 row(s) inserted.
• Statement 8
Insert EMP row, using TO_DATE function to cast string literal into an oracle DATE format.
``````insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
)``````
1 row(s) inserted.
• Statement 9
``````insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'),
2850, null, 30
)``````
1 row(s) inserted.
• Statement 10
``````insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10
)``````
1 row(s) inserted.
• Statement 11
``````insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'),
2975, null, 20
)``````
1 row(s) inserted.
• Statement 12
``````insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
)``````
1 row(s) inserted.
• Statement 13
``````insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'),
3000, null, 20
)``````
1 row(s) inserted.
• Statement 14
``````insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
to_date('17-12-1980','dd-mm-yyyy'),
800, null, 20
)``````
1 row(s) inserted.
• Statement 15
``````insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'),
1600, 300, 30
)``````
1 row(s) inserted.
• Statement 16
``````insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
to_date('22-2-1981','dd-mm-yyyy'),
1250, 500, 30
)``````
1 row(s) inserted.
• Statement 17
``````insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-9-1981','dd-mm-yyyy'),
1250, 1400, 30
)``````
1 row(s) inserted.
• Statement 18
``````insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-9-1981','dd-mm-yyyy'),
1500, 0, 30
)``````
1 row(s) inserted.
• Statement 19
``````insert into emp
values(
to_date('13-JUL-87', 'dd-mm-rr') - 51,
1100, null, 20
)``````
1 row(s) inserted.
• Statement 20
``````insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
to_date('3-12-1981','dd-mm-yyyy'),
950, null, 30
)``````
1 row(s) inserted.
• Statement 21
``````insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
to_date('23-1-1982','dd-mm-yyyy'),
1300, null, 10
)``````
1 row(s) inserted.
• Statement 22
Simple natural join between DEPT and EMP tables based on the primary key of the DEPT table DEPTNO, and the DEPTNO foreign key in the EMP table.
``````select ename, dname, job, empno, hiredate, loc
from emp, dept
where emp.deptno = dept.deptno
order by ename``````
ENAMEDNAMEJOBEMPNOHIREDATELOC
ALLENSALESSALESMAN749920-FEB-81CHICAGO
BLAKESALESMANAGER769801-MAY-81CHICAGO
CLARKACCOUNTINGMANAGER778209-JUN-81NEW YORK
FORDRESEARCHANALYST790203-DEC-81DALLAS
JAMESSALESCLERK790003-DEC-81CHICAGO
JONESRESEARCHMANAGER756602-APR-81DALLAS
KINGACCOUNTINGPRESIDENT783917-NOV-81NEW YORK
MARTINSALESSALESMAN765428-SEP-81CHICAGO
MILLERACCOUNTINGCLERK793423-JAN-82NEW YORK
SCOTTRESEARCHANALYST778819-APR-87DALLAS
SMITHRESEARCHCLERK736917-DEC-80DALLAS
TURNERSALESSALESMAN784408-SEP-81CHICAGO
WARDSALESSALESMAN752122-FEB-81CHICAGO

14 rows selected.
• Statement 23
The GROUP BY clause in the SQL statement allows aggregate functions of non grouped columns. The join is an inner join thus departments with no employees are not displayed.
``````select dname, count(*) count_of_employees
from dept, emp
where dept.deptno = emp.deptno
group by DNAME
order by 2 desc``````
DNAMECOUNT_OF_EMPLOYEES
SALES6
RESEARCH5
ACCOUNTING3

3 rows selected.
• Statement 24
``````SELECT EMPNO, DEPTNO, SAL , PERCENTILE_DISC(0.5)

WITHIN GROUP (ORDER BY SAL DESC)

OVER (PARTITION BY DEPTNO) "Percentile_DISC",percentile_cont(0.5) within group (order by sal desc) OVER (PARTITION BY DEPTNO) "median cont"

FROM EMP``````
EMPNODEPTNOSALPercentile_DISCmedian cont
793410130024502450
778210245024502450
783910500024502450
73692080029752975
787620110029752975
756620297529752975
778820300029752975
790220300029752975
79003095015001375
765430125015001375
752130125015001375
784430150015001375
749930160015001375
769830285015001375

14 rows selected.