In SQL, you use the
HAVING keyword right after
GROUP BY to query the database based on a specified condition. Like other keywords, it returns the data that meet the condition and filters out the rest.
HAVING keyword was introduced because the
WHERE clause fails when used with aggregate functions. So, you have to use the
HAVING clause with aggregate functions instead of
HAVING clause, you can arrange the data in your database into many groups when you use it with the
GROUP BY keyword. So, you can use it in a large database.
How to Use the
Suppose I have a table named
students in a
SELECT * FROM students returns the following:
You can get only the names and scores by running
SELECT name, score FROM students.
You can then use the
HAVING keyword to filter out some students based on a condition. For example, those who have a score greater than 70.
But before that, you must use the
GROUP BY clause like this:
GROUP BY name, score
This won’t return anything yet. So you need to bring in the
HAVING score > 70
Now, I’m able to get students who scored higher than 70:
The full query looks like this:
SELECT name, score FROM students GROUP BY name, score HAVING score > 70
I’m also able to get the students who scored more than 90 this way:
SELECT name, score FROM students GROUP BY name, score HAVING score > 90
The table also has an age column, so I can get the students who are over 14 years this way:
SELECT name, age FROM students GROUP BY name, age HAVING age > 14
An error occurs if you use
WHERE with an aggregate function
SELECT name, count(*) FROM students GROUP BY name WHERE COUNT(*) > 0
The error goes away if you use
SELECT name, count(*) FROM students GROUP BY name HAVING COUNT(*) > 0
You can use any operator you want!
The operator is not exclusive to comparisons. So, I’m able to get students who are 13 years by changing the HAVING statement to
HAVING age = 13:
I got the students who scored 90 this way:
SELECT name, score FROM students GROUP BY name, score HAVING score = 90
If the condition in the HAVING statement is not met, no row will be returned:
SELECT name, score FROM students GROUP BY name, score HAVING score = 100
An Error Occurs if you Use
SELECT COUNT(*) FROM students HAVING score > 80
In this case, you have to use the
SELECT COUNT(*) FROM students WHERE score > 80
In this article, you learned how to query databases using the
Remember that you have to use the
HAVING clause with
GROUP BY so you can get the desired data, just as you’ve seen in this article.
In situations where you can’t use the
HAVING clause, you probably need to use
Thank you for reading.