CODEWITHSUNDEEP
sqloracle-database
Kenertj
Kenertj

Reputation: 57

SQL multible select statements in one table

I have a SQL database with 2 tables called DEPT and EMP

  create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
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)  
);

What needs to be done is the following:

This is what I have ready so far

SELECT ROUND(AVG(sal) over(),2) AS COMPANY_AVERAGE_SALARY,sal - ROUND(AVG(sal) over(),2) AS DEVIATION_FROM_COMPANY_AVERAGE_SALARY, empno AS EMPNO, job AS JOB, sal AS SALARY  from emp;

I also have:

SELECT dept.loc AS DEPARTMENT_LOCATION FROM emp,dept WHERE emp.deptno=Dept.deptno;

My main problem is that I can't seem to get the different select lines to display in the same output and don't know how to get department average salary to display on each line and how to do DEVIATION_FROM_DEPARTMENT_AVERAGE_SALARY Would appreciate the help a lot.

Upvotes: 0

Views: 59

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6094

You were very close with your example queries. To find the deviation, you just need to find the difference between the current employee salary and the company/dept average salary like this:

SELECT d.loc
           AS department_location,
       ROUND (AVG (sal) OVER (), 2)
           AS company_average_salary,
       ROUND (AVG (sal) OVER (PARTITION BY e.deptno), 2)
           AS dept_average_salary,
       ROUND (sal - AVG (sal) OVER (PARTITION BY e.deptno), 2)
           AS dept_salary_deviation,
       ROUND (sal - AVG (sal) OVER (), 2)
           AS company_salary_deviation,
       e.empno,
       e.job,
       e.sal
  FROM emp e LEFT JOIN dept d ON (e.deptno = d.deptno);

Upvotes: 2

Related Questions