w3resource
SQL exercises

SQL exercises on employee Database: List the no. of employees and average salary within each department for each job name

SQL employee Database: Exercise-104 with Solution

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

104. Write a query in SQL to list the no. of employees and average salary within each department for each job name.

Sample table: employees


Sample Solution:

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

Sample Output:

 count |          avg          | dep_id | job_name
-------+-----------------------+--------+-----------
     1 | 2750.0000000000000000 |   3001 | MANAGER
     2 | 3100.0000000000000000 |   2001 | ANALYST
     4 | 1500.0000000000000000 |   3001 | SALESMAN
     1 | 2550.0000000000000000 |   1001 | MANAGER
     1 | 6000.0000000000000000 |   1001 | PRESIDENT
     1 | 2957.0000000000000000 |   2001 | MANAGER
     2 | 1050.0000000000000000 |   2001 | CLERK
     1 | 1400.0000000000000000 |   1001 | CLERK
     1 | 1050.0000000000000000 |   3001 | CLERK
(9 rows)

Practice Online


Sample Database: employee

employee database structure

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

Previous: Write a query in SQL to check whether all the employees numbers are indeed unique.
Next: Write a query in SQL to list the names of those employees starting with 'A' and with six characters in length.

What is the difficulty level of this exercise?