Oracle : It give a error in a dynamic view

Question

I have two table one is employee and one is department. I am creating the dynamic view that will rank all departments by salary. The view should pull information from Department and Employee, sum the salary by department, and rank the department by salary.

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30) NOT NULL
);

CREATE TABLE JOBS
(JOB_ID NUMBER PRIMARY KEY,
JOB_TITLE VARCHAR(35) NOT NULL,
MIN_SALARY DECIMAL NOT NULL,
MAX_SALARY DECIMAL NOT NULL
);

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
EMAIL VARCHAR(25) NOT NULL,
PHONE_NUMBER VARCHAR(20) NOT NULL,
HIRE_DATE DATE NOT NULL,
JOB_ID NUMBER NOT NULL,
SALARY DECIMAL NOT NULL,
DEPARTMENT_ID NUMBER NOT NULL,
CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
);

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');

INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (1,'IT Administrator',250000.00,50000.00);
INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (2,'Salesman',200000.00,40000.00);

Here is I create so far but it give me a error

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action: Error at Line: 4 Column: 9

Here is my code

select department_id,department_name,total_salary 
from(

select  department_id,department_name, SALARY, count(*) as total_salary from(
select dep.department_id , dep.department_name ,emp.SALARY,
DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary)
from departments dep

inner join employees emp on dep.DEPARTMENT_ID = emp.DEPARTMENT_ID

)
GROUP BY SALARY)

Possible duplicate of [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — OldProgrammer, May 21 '18 at 20:29
Pls check your subqueries! The innermost one is a join and is fine. The middle one use `count(*)` but has no `GROUP BY` - **problem**. Read t*m what is the role of `GROUP BY`. — Marmite Bomber, May 21 '18 at 21:01
What's a dynamic view? How is it different from a normal view? — William Robertson, May 21 '18 at 23:12

score 0 · Answer 1 · answered May 22 '18 at 08:06

Your query needs to join EMPLOYEES (to get the salaries) to DEPARTMENT (to get the DEPARTMENT_NAME). Calculate the total salary for each department by summing the employee salaries, not counting them. The GROUP BY needs to include the non-aggregated columns.

Then you need to rank the departments by the total salary per department. This query ranks the departments with highest salary = 1. It uses a left join to cater for departments with no employees.

select department_id
       , department_name
       , total_salary
       , rank() over (order by total_salary desc) as dept_rank 
from (
     select  d.department_id
             , d.department_name
             , sum(e.SALARY)  as total_salary 
     from department d
          left join employees e
           on e.department_id = d.department_id 
      group by d.department_id
             , d.department_name
     )
/

Oracle : It give a error in a dynamic view

1 Answers1