CODEWITHSUNDEEP
mysqlsql
SixTwentyFour
SixTwentyFour

Reputation: 41

How to work with result of count function in MySQL?

I need to do a query counting how many employees are in each department that have more than 1 employee and with employee numbers 6 or greater, I have: 

 Select DepartmentName, count(EmployeeNumber) as "Number of Employees"
 From department d, employee e
 Where d.DepartmentName = e.Department and e.EmployeeNumber <=6 
 Group by d.DepartmentName;

This fills all requirements except for the part about having more than 1 employee, Is there a way to only execute the rest of the code for results that return 2 or greater from the first line?

Upvotes: 1

Views: 171

Answers (2)

echo_Me
echo_Me

Reputation: 37253

try this

    Select DepartmentName, count(EmployeeNumber) as "Number of Employees"
    From department d, employee e
    Where d.DepartmentName = e.Department  
    Group by d.DepartmentName and e.EmployeeNumber <=6
    HAVING count(EmployeeNumber) > 1 ;

Upvotes: 1

Bart Friederichs
Bart Friederichs

Reputation: 33531

Use the HAVING clause. Note though that it is applied after the whole thing is counted.

Select DepartmentName, count(EmployeeNumber) as "Number of Employees"
From department d, employee e
Where d.DepartmentName = e.Department and e.EmployeeNumber <=6 
Group by d.DepartmentName HAVING count(EmployeeNumber) > 1;

Upvotes: 2

Related Questions