How do I find the salary of different job positions in the same department?

November 12, 2022

Using oracle’s HR Database I want to find the salaries (just a sum of all the salaries) of every job position in a department, the result should be something like this

I have absolutely no idea about what to do here.

>Solution :

I don’t have HR schema so here’s how it would look like in Scott’s. Adjust it to your data model.

Join departments to employees (so that you could fetch department name), get the job and compute sum of all salaries. Columns that aren’t aggregated have to be part of the group by clause.

SQL> select d.dname, e.job, sum(e.sal)
  2  from dept d join emp e on e.deptno = d.deptno
  3  group by d.dname, e.job
  4  order by d.dname, e.job;

DNAME          JOB       SUM(E.SAL)
-------------- --------- ----------
ACCOUNTING     CLERK         1300.1
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
RESEARCH       ANALYST         6000
RESEARCH       CLERK         1900.2
RESEARCH       MANAGER         2975
SALES          CLERK          950.1
SALES          MANAGER         2850
SALES          SALESMAN      5600.4

9 rows selected.

SQL>