sqloracle-databaseora-00979

i got error ORA-00979: not a GROUP BY expression

I got Error ORA-00979 with the following query:

SELECT d.department_name "department name",
       e.job_id "job title",
       SUM(e.salary) "monthly cost", 
       GROUPING(d.department_id) "Department ID Used",
       GROUPING(e.job_id) "Job ID Used"
FROM   employees e
       JOIN departments d
       ON e.department_id=d.department_id
GROUP BY cube(d.department_name, e.job_id)
ORDER BY d.department_name, e.job_id

Is any wrong with query?

Solution

  • You are using GROUPING(d.department_id) but department_id is not in the GROUP BY clause.

    You could add it to the GROUP BY clause:

    SELECT d.department_name "department name",
       e.job_id "job title",
       SUM(e.salary) "monthly cost",
       GROUPING(d.department_id) "Department ID Used",
       GROUPING(e.job_id) "Job ID Used"
FROM   employees e
       JOIN departments d
       ON e.department_id=d.department_id
GROUP BY cube(d.department_name, d.department_id, e.job_id)
ORDER BY d.department_name, e.job_id

    db<>fiddle here