# Find Average Salary

### **Problem Statement:**

Write a query to find the **average** **salary** of the employees for **each** department.

* Save the new average salary as '**Average\_salary**'.
    
* Return the columns '**department\_id**', '**department\_name**', and '**Average\_salary**'.
    
* Return the result ordered by **department\_id** in ascending order.
    

### **Dataset Description:**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1721148456357/a30607c7-1f61-4e4a-aba4-f2beb4855c36.png align="center")

**Sample Input:**

**Table**: employees

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1721148483081/7daee61c-b47f-4179-8adf-42b86355eb02.png align="center")

**Table**: departments

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1721148503825/a253eb84-28ae-4da3-b454-df986e8b85b6.png align="center")

**Sample Output:**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1721148521769/3ae91ea6-bc78-470e-b789-376f4f4807a3.png align="center")

### Approach 1: SQL Join Syntax

```sql
SELECT
    e.department_id,
    d.department_name,
    AVG(e.salary) AS Average_salary
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    e.department_id,
    d.department_name
ORDER BY
    e.department_id;
```

**Explanation:**

This query uses an explicit `JOIN` clause to join the `employees` and `departments` tables. This approach is more modern and generally preferred for clarity and maintainability.

### Approach 2: Use Common Table Expression (CTE):

```sql
sqlCopy codeWITH DepartmentSalaries AS (
    SELECT
        e.department_id,
        d.department_name,
        e.salary
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT
    department_id,
    department_name,
    AVG(salary) AS Average_salary
FROM
    DepartmentSalaries
GROUP BY
    department_id,
    department_name
ORDER BY
    department_id;
```

**Explanation:**

This approach uses a CTE to first select the relevant data from the `employees` and `departments` tables, and then the outer query performs the aggregation and sorting. This can sometimes make complex queries easier to understand and maintain.

> The article presents two approaches to find the average salary by department in a SQL database: using explicit JOIN syntax and utilizing a Common Table Expression (CTE). The dataset includes 'employees' and 'departments' tables, and the desired output is columns for 'department\_id', 'department\_name', and 'Average\_salary', sorted by 'department\_id'.