CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
rrrawr
rrrawr

Reputation: 3

Cube SQL ORA-00936 missing expression

im trying using cube in my query but it didnt work this is my query

select department_id, job_id , salary/12 as "monthly_salary"
from employees
where department_id
(select department_id from departments)
group by cube(department_id,job_id)
order by department_id,job_id;

by using 2 tables which is employees and departments forgive me if im asking too much,im still new to this :)

Upvotes: 0

Views: 72

Answers (2)

Littlefoot
Littlefoot

Reputation: 142798

You're missing

  • IN keyword (for list of departments)
  • SUM function for salaries
    • I included ROUND so that it looks prettier

So:

  SELECT department_id, 
         job_id, 
         ROUND (SUM (salary) / 12) monthly_salary
    FROM employees
   WHERE department_id IN (SELECT department_id FROM departments)
GROUP BY CUBE (department_id, job_id)
ORDER BY department_id, job_id

Upvotes: 0

Connor McDonald
Connor McDonald

Reputation: 11591

Not entirely sure what you are asking, but here is an example of a cube to get totals by deptno, job and the combination of the two

SQL> select deptno,job,sum(sal) from scott.emp
  2  group by CUBE(deptno,job)
  3  order by deptno,job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
           ANALYST         6000
           CLERK           4150
           MANAGER         8275
           PRESIDENT       5000
           SALESMAN        5600
                          29025

Upvotes: 1

Related Questions