August 21, 2010
SQL Having Clause
Problem
You have the following database table: employee (emp_name, emp_age, emp_department) Write an SQL statement to calculate the average age of employees who are older than 30 years old for each department. Do not display the average if the number of employees used in the calculation is less than 3.
Solution
You need to use (group by) clause to break the calculation by department and the (having) clause to exclude averages when the number of employees involved in the calculation is less than 3
Code
Here is the SQL statement to do that
1 2 3 4 5 |
Select emp_department, AVG(emp_age) from employee where emp_age >= 30 group by emp_department having count() >= 2 |