SQL Having

SQL Having

This page covers SQL Having clause.

The statement “having” adds a specified condition to a “select” statement.

It allows filtering data with grouped or aggregated query.

Let’s use the table “student_info” from the previous tutorials.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

Having

The clause “having” can help when retrieving data through the “select” statement.

It is often used with the “group by” clause.

Let’s look at a previous example from the group by tutorial.

The scenario retrieves all student ages, and summarises them into sorted groups.

SELECT COUNT(age) AS n, age FROM student_info
GROUP BY age
ORDER BY COUNT(age);

n	age
0	NULL
1	19
1	26
2	21

If we want to search and select only age groups that have a minimum of student records, we cannot implement “COUNT(age) > 1” to a “select” or “where” statement.

The below query produces an error.

SELECT COUNT(age) > 1 AS n, age FROM student_info
GROUP BY age
ORDER BY COUNT(age);

The below query produces an error.

SELECT COUNT(age)AS n, age FROM student_info
WHERE COUNT(age) > 1
GROUP BY age
ORDER BY COUNT(age);

The above queries can work with the “having” statement.

Let’s look at a successfully executing example.

SELECT COUNT(age)AS n, age FROM student_info
GROUP BY age
HAVING COUNT(age) > 1
ORDER BY COUNT(age);

n	ages
2	21

The query filters through the data and selects only age groups that have at least 2 student records. The table contains 2 students that have 21 years of age.

---

Next: SQL Set