SQL Exercise: List average salary of employees in department wise

SQL subqueries on employee Database: Exercise-37 with Solution

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

37. From the following table, write a SQL query to compute department wise average salary of employees. Return employee name, average salary, department ID as "Current Salary".

Sample table: employees

Sample Solution:

SELECT e.emp_name,
       d.avgsal,
       e.dep_id AS "Current Salary"
FROM employees e,

  (SELECT avg(salary) avgsal,
          dep_id
   FROM employees
   GROUP BY dep_id) d
WHERE e.dep_id=d.dep_id;

Sample Output:

 emp_name |        avgsal         | Current Salary
----------+-----------------------+----------------
 KAYLING  | 3316.6666666666666667 |           1001
 BLAZE    | 1633.3333333333333333 |           3001
 CLARE    | 3316.6666666666666667 |           1001
 JONAS    | 2251.4000000000000000 |           2001
 SCARLET  | 2251.4000000000000000 |           2001
 FRANK    | 2251.4000000000000000 |           2001
 SANDRINE | 2251.4000000000000000 |           2001
 ADELYN   | 1633.3333333333333333 |           3001
 WADE     | 1633.3333333333333333 |           3001
 MADDEN   | 1633.3333333333333333 |           3001
 TUCKER   | 1633.3333333333333333 |           3001
 ADNRES   | 2251.4000000000000000 |           2001
 JULIUS   | 1633.3333333333333333 |           3001
 MARKER   | 3316.6666666666666667 |           1001
(14 rows)

Explanation:

The said query in SQL that creates a report with columns "emp_name", "avgsal", and "dep_id" aliased as 'Current Salary' from the 'employees' table and a derived table created from a subquery that lists all employees with their respective departments and the average salary for that department.

The query joins the 'employees' table with the derived table 'd' on the department ID column. The result set will include all employees and their respective departments, as well as the average salary for their department.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Salary is less than manager but more than colleagues.
Next SQL Exercise: Find out the least 5 earners of the company.