SQL: department name with minimum of 10 employees

Question

I have a table for employees and another for department names. The constructions are

employees - employee_id - employee_name - department_id

departments - department_id - department_name

I want to show a table with department names where more than 10 employees work.

The query I tried without results is:

select count(*) as count,d.department_name
from employees e
inner join departments d on e.department_id = d.department_id 
where count(*) > 5
group by d.department_name

please suggest correction

Answer 1

There is a specific clause that occurs after group by for this, the HAVING clause, that allows filtering on aggregated values.

select count(*) as count,d.department_name
from employees e
inner join departments d on e.department_id = d.department_id 
group by d.department_name
HAVING count(*) > 10

NB: The where clause should continue to be used for conditions that do not rely on aggregates. e.g. if you just wanted "sales related" departments

select count(*) as count,d.department_name
from employees e
inner join departments d on e.department_id = d.department_id 
WHERE d.department_name like 'sales%'
group by d.department_name
HAVING count(*) > 10