The HAVING Clause
The HAVING Clause is used alongside GROUP BY and Aggregate Functions. Its used to filter groups based on based on an aggregate result.
Lets look at this through a question:
Q. Display the count of customers of age groups which has more than 1 customer.
S.
SELECT age, COUNT(*) FROM customers GROUP BY age HAVING COUNT(*) > 1;
This will output :
| age | COUNT(*) |
|---|---|
| 30 | 2 |
What's happening here is - I am displaying all the ages and the COUNT of customers in it but I am only displaying the age group where the number of customers is more than 1;
The HAVING Clause is designed to work with Aggregates because the WHERE Clause can't do it.
Doing something like:
SELECT age, COUNT(*) FROM customers GROUP BY age WHERE COUNT(*) > 1; /* ERROR */
will simply thrown an error and won't work.
This is the Rule - We must use HAVING while comparing aggregate results.
Lets look at another question before wrapping this off:
Q. Write a query to:
- Find all ages where more than 1 person exists,
- Show the
ageand the number of people astotal, - Order the results by
totaldescending, - And alias the count column as
total.
S.
SELECT age, COUNT(*) as total FROM customers GROUP BY age HAVING COUNT(*) > 1 ORDER BY total DESC;
That's it for the HAVING Clause.
Follow SQL Joins to continue from here.
Browse Full SQL Page.