SQL HAVING – How to Group and Count with a Having Statement

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.

The 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 WHERE.

With the 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 HAVING Keyword.

Suppose I have a table named students in a student_scores database.
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 keyword:

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 HAVING:

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 HAVING without GROUP BY

SELECT COUNT(*)
FROM students 
HAVING score > 80

In this case, you have to use the WHERE clause:

SELECT COUNT(*)
FROM students 
WHERE score > 80

Wrapping Up

In this article, you learned how to query databases using the HAVING keyword.

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 WHERE.

Thank you for reading.