SQL Practice Problem #007 - Average salary by department

Problem

Task

Calculate the average salary for each department.

Schema

Table Schema

employees(id, name, department, salary, manager_id)

Input

Sample Data

id	name	department	salary	manager_id
1	Mina	Engineering	70000	NULL
2	Daniel	Engineering	80000	1
3	Sofia	Marketing	60000	NULL

Output

Expected Output

department	average_salary
Engineering	75000
Marketing	60000

Answer

Check Your Solution

Show Answer and Explanation

Correct Answer

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Explanation

AVG is an aggregate function. Because the query also returns department, each department must be grouped so the database knows which rows belong together.

Common Mistakes

Selecting name together with department and AVG(salary).
Forgetting GROUP BY department.
Using SUM(salary) when the problem asks for an average.

Concepts

Related Concepts

AVG GROUP BY Aggregate Functions Alias

Next practice