Filtering Results

Aggregated data can be filtered in two ways to allow you complete flexibility within SELECT statements:

• You can use the WHERE clause to filter rows before they are grouped. Rows excluded by the WHERE condition are not included in aggregate calculations and processing. WHERE conditions can be used regardless of whether grouping is used and can reference any valid expression.

• You can use the HAVING clause to filter entire groups. This condition is applied after groups have been processed to eliminate aggregate values that do not match a specific condition. HAVING can be used only in conjunction with grouping and can reference any items in the select list.

The WHERE clause was reviewed in Chapter 44, "Basic SQL."

The following example returns the number of employees in each department, including only employees residing in the specified country:

 SELECT department_name, COUNT(*) AS num_employees FROM employees, departments WHERE employees.department_id=departments.department_id  AND employees.country='USA' GROUP BY department_name ORDER BY department_name 

This next example returns the number of employees in each department that has 100 or more employees:

 SELECT department_name, COUNT(*) AS num_employees FROM employees, departments WHERE employees.department_id=departments.department_id GROUP BY department_name HAVING COUNT(*) >= 100 ORDER BY department_name 

NOTE

You can use both WHERE and HAVING in the same SELECT statement if necessary. WHERE must always be the first clause after the tables (specified in the FROM clause).