SQL exercises on employee Database: display the number of employee for each job in each department

SQL employee Database: Exercise-97 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

97. Write a query in SQL to display the number of employee for each job in each department.

Sample table: employees

Sample Solution:

SELECT dep_id,
       job_name,
       count(*)
FROM employees
GROUP BY dep_id,
         job_name;

Sample Output:

 dep_id | job_name  | count
--------+-----------+-------
   3001 | MANAGER   |     1
   2001 | ANALYST   |     2
   3001 | SALESMAN  |     4
   1001 | MANAGER   |     1
   1001 | PRESIDENT |     1
   2001 | MANAGER   |     1
   2001 | CLERK     |     2
   1001 | CLERK     |     1
   3001 | CLERK     |     1
(9 rows)

Practice Online

Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to list the manager no and the number of employees working for those managers in ascending order on manager id.
Next: Write a query in SQL to list the department where at least two employees are working.