Schema and sample data
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Ava Torres | Engineering | 100000.00 |
| 2 | Ben Ncube | Sales | 50000.00 |
| 3 | Cara Singh | Engineering | 110000.00 |
| 4 | Deng Liu | Support | 45000.00 |
| 5 | Emma Rossi | Sales | 60000.00 |
| 6 | Farid Khan | Engineering | 90000.00 |
| 7 | Gita Patel | Support | 55000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30),
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, department, salary) VALUES
(1, 'Ava Torres', 'Engineering', 100000.00),
(2, 'Ben Ncube', 'Sales', 50000.00),
(3, 'Cara Singh', 'Engineering', 110000.00),
(4, 'Deng Liu', 'Support', 45000.00),
(5, 'Emma Rossi', 'Sales', 60000.00),
(6, 'Farid Khan', 'Engineering', 90000.00),
(7, 'Gita Patel', 'Support', 55000.00);From the Employees table, return each department together with the average salary of its employees. Alias the average column as avg_salary. Sort the result by avg_salary from highest to lowest.
Expected result
| department | avg_salary |
|---|---|
| Engineering | 100000.00 |
| Sales | 55000.00 |
| Support | 50000.00 |
Show hint
Collapse the rows into one group per department with GROUP BY, then apply AVG(salary) to each group. Every column in the SELECT must either be grouped or wrapped in an aggregate.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT department, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department
ORDER BY avg_salary DESC;GROUP BY department folds the seven employee rows into one row per distinct department: Engineering, Sales and Support. AVG(salary) is then computed independently for each group. Engineering averages (100000 + 110000 + 90000) / 3 = 100000, Sales averages (50000 + 60000) / 2 = 55000, and Support averages (45000 + 55000) / 2 = 50000.
The average is aliased as avg_salary with AS, and because the alias names a value that exists after grouping, ORDER BY avg_salary DESC can sort the groups from highest average to lowest. See the AVG function reference for how it ignores NULL salaries when counting the divisor.
A common mistake is to add a non aggregated column such as name to the SELECT without adding it to GROUP BY, which raises an error because a single grouped row cannot map to one specific employee name. Only the grouping key and aggregate expressions belong in a grouped query.