Schema and sample data
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Ava Torres | Engineering | 72000.00 |
| 2 | Ben Ncube | Sales | 48000.00 |
| 3 | Cara Singh | Engineering | 95000.00 |
| 4 | Deng Liu | Support | 50000.00 |
| 5 | Emma Rossi | Sales | 61000.00 |
| 6 | Finn Walsh | Engineering | 68000.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', 72000.00),
(2, 'Ben Ncube', 'Sales', 48000.00),
(3, 'Cara Singh', 'Engineering', 95000.00),
(4, 'Deng Liu', 'Support', 50000.00),
(5, 'Emma Rossi', 'Sales', 61000.00),
(6, 'Finn Walsh', 'Engineering', 68000.00);From the Employees table, return each department together with the number of employees in it. Name the count column num_employees. Sort the result so the department with the most employees appears first (count from highest to lowest).
Expected result
| department | num_employees |
|---|---|
| Engineering | 3 |
| Sales | 2 |
| Support | 1 |
Show hint
When a question asks for a value "per" something, you almost always need GROUP BY on that column plus an aggregate. Group by department, use COUNT(*) to count the rows in each group, and give the count an alias so you can sort by it.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
ORDER BY num_employees DESC;GROUP BY department collapses the rows into one group per distinct department: Engineering, Sales and Support. COUNT(*) then counts the rows inside each group, giving 3 for Engineering, 2 for Sales and 1 for Support. The AS num_employees alias names that computed column so the output is readable and can be referenced by ORDER BY. See COUNT for how the function treats rows and NULLs.
ORDER BY num_employees DESC sorts the grouped result from the largest count to the smallest, so Engineering lands on top. In SQL Server you can order by the alias here because ORDER BY is logically processed after the SELECT list, so the alias is already visible.
A common mistake is putting a non aggregated column such as name in the SELECT list without adding it to GROUP BY: T-SQL rejects that with error 8120 because a single group can contain many different names. Only the grouping column and aggregates belong in the select list. Note also the difference between COUNT(*), which counts every row in the group, and COUNT(column), which skips rows where that column is NULL.