# 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
TUCKER   | 1633.3333333333333333 |           3001
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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿