Developing By Me

Project Development and Design

HAVING Clause

HAVING Clause

The HAVING clause is used with aggregate functions such as COUNT() or SUM(). This clause works like the WHERE and uses the same logic. The difference is that any calculated values cannot be used within the WHERE clause and must be handled in the HAVING clause. Any values calculated require the GROUP BY clause to handle the function and those values can then be filtered by the HAVING clause. The logic is fairly simple and the following query shows how the GROUP BY and HAVING clause work as a pair.

 

SELECT COUNT(field1) AS ‘Count’, field1
FROM table AS t
GROUP BY field1
HAVING COUNT(field1) < 100

 

For the query above the result set will include two fields. The first is the count of all rows with the same field1, which is the second returned field. The GROUP BY clause does this organization and allows for the calculation. The final step is that the HAVING clause will limit the returned dataset with only a count under 100. This is a classic example of what the HAVING clause is used for and there are others but the simple logic displayed is how it can be used with other functions as well. The last thing to know about the HAVING clause is that if the GROUP BY is missing SQL will throw an error because the calculations cannot be handled.

 

%d bloggers like this: