Write an SQL Query to Retrieve Departments with More Than Three Employees

Last Post

RSS

Posts: 71

Vinay Kumar

Guest

Topic starter

Nov 09, 2024 6:34 am

(@Vinay Kumar)

Trusted Member

Joined: 4 years ago

How can you write an SQL query to find departments that have more than three employees? Assume you have an employees table with columns employee_id, department, and employee_name.

Answer:

To retrieve departments with more than three employees, we can use the GROUP BY clause with the HAVING condition to filter groups based on the count of employees.

Solution: Using GROUP BY and HAVING

Assume the employees table structure is as follows:

employee_id	department	employee_name
1	HR	Alice
2	IT	Bob
3	IT	Carol
4	IT	Dave
5	Sales	Eve
6	Sales	Frank
7	Sales	Grace
8	Sales	Helen

In this example, the IT and Sales departments have more than three employees.

SQL Query:

Explanation:

GROUP BY department: This groups the records by department.
COUNT(employee_id): Counts the number of employees in each department.
HAVING COUNT(employee_id) > 3: The HAVING clause filters out departments with fewer than four employees, returning only those with more than three employees.

Expected Output:

Based on the example data, the result would look like:

department	employee_count
IT	3
Sales	4

Alternative Solution: Using a Subquery

If you need a list of employee names from departments with more than three employees, you can use a subquery.

SQL Query for Alternative Solution:

Explanation of Alternative Solution:

Subquery for Departments with More than Three Employees: The inner query retrieves departments that meet the condition.
Main Query Filters Employees: The main query selects employees from only those departments.