HAVING

19min
We have a WHERE clause to filter out rows based on a specified condition, but we also have a HAVING clause that specifies a search condition for a group or an aggregate. Furthermore, this section will guide you on how to use the HAVING clause.
OverviewThe HAVING clause specifies a search condition by using an aggregate function. It will filter out the records returned from a GROUP BY clause that do not fulfill a specified condition.
Differences Between WHERE and HAVING ClauseThe following table will illustrate the differences between the HAVING and WHERE clause:
WHERE
HAVING
The GROUP BY clause appears after the WHERE clause.
The GROUP BY clause appears before the HAVING clause.
The WHERE clause can’t work with an aggregate function.
The HAVING clause can work with an aggregate function.
﻿
The WHERE clause filters particular records.
The HAVING clause filters the group of records.
﻿
SyntaxThe basic syntax of the GROUP BY clause is as follows:
Syntax
|
SELECT column_1, column_2,...
FROM table_name
GROUP BY column_name(s)
HAVING condition_aggregate_function
SELECT column_1, column_2,...
FROM table_name
GROUP BY column_name(s)
HAVING condition_aggregate_function
﻿
Let’s explore the above syntax:
SELECT column_1, column_2,... selects the columns you want to display.

FROM table_name selects the table where the data comes from.

GROUP BY column_name(s)  lists the columns you want to group in the GROUP BY clause.

HAVING condition_aggregate_function provides the condition for filtering rows, which the GROUP BY clause forms. The condition can use an aggregate function, such as SUM(), COUNT(), MIN(), and so on.
ExamplesLet’s assume that we have two tables here, the student table and the score table:
                                                                                         student table﻿
Create a table
|
CREATE TABLE student (
  stud_id int,
  stud_name string
);
INSERT INTO student 
    (stud_id, stud_name) 
VALUES 
    (992831192, 'Mary'),
    (992811191, 'Bobby'),
    (992311195, 'Sean'),
    (998311193, 'Harry'),
    (998311194, 'William'),
    (928311197, 'Kate'),
    (928311190, 'Tom'),
    (928311199, 'Sully'),
    (998311196, 'Susan');
CREATE TABLE student (
  stud_id int,
  stud_name string
);
INSERT INTO student 
    (stud_id, stud_name) 
VALUES 
    (992831192, 'Mary'),
    (992811191, 'Bobby'),
    (992311195, 'Sean'),
    (998311193, 'Harry'),
    (998311194, 'William'),
    (928311197, 'Kate'),
    (928311190, 'Tom'),
    (928311199, 'Sully'),
    (998311196, 'Susan');
﻿
Display the table
|
SELECT * FROM student;
SELECT * FROM student;
﻿
 It will create a table as shown below:                                                                 
The student table
|
+------------+------------+
| stud_id    | stud_name  |
+------------+------------+
| 992831192  | Mary       |
| 992811191  | Bobby      |
| 992311195  | Sean       |
| 998311193  | Harry      |
| 998311194  | William    |
| 928311197  | Kate       |
| 928311190  | Tom        |
| 928311199  | Sully      |
| 998311196  | Susan      |
+------------+------------+
+------------+------------+
| stud_id    | stud_name  |
+------------+------------+
| 992831192  | Mary       |
| 992811191  | Bobby      |
| 992311195  | Sean       |
| 998311193  | Harry      |
| 998311194  | William    |
| 928311197  | Kate       |
| 928311190  | Tom        |
| 928311199  | Sully      |
| 998311196  | Susan      |
+------------+------------+
﻿
                                                                                      score table﻿
Create a table
|
CREATE TABLE score (
  score_id int,
  subject string,
  score_val int,
  stud_id int,
  score_stat string
);
INSERT INTO score 
    (score_id, subject, score_val, stud_id, score_stat) 
VALUES 
    (12221, 'Math', 90, 992811191, 'PASSED'),
    (12222, 'Biology', 90, 992811191, 'PASSED'),
    (12223, 'Art', 80, 992831192, 'PASSED'),
    (12224, 'History', 70, 928311197, 'FAILED'),
    (12225, 'Pyshics', 75, 928311190, 'FAILED'),
    (12226, 'Art', 85, 928311197, 'PASSED'),
    (12227, 'Biology', 90, 998311196, 'PASSED'),
    (12228, 'Biology', 70, 928311199, 'FAILED'),
    (12229, 'Pyshics', 80, 998311194, 'PASSED'),
    (12231, 'Math', 80, 998311193, 'PASSED'),
    (12232, 'History', 90, 992811191, 'PASSED'),
    (12233, 'Math', 70, 998311194, 'FAILED'),
    (12234, 'Math', 80, 928311190, 'PASSED');
CREATE TABLE score (
  score_id int,
  subject string,
  score_val int,
  stud_id int,
  score_stat string
);
INSERT INTO score 
    (score_id, subject, score_val, stud_id, score_stat) 
VALUES 
    (12221, 'Math', 90, 992811191, 'PASSED'),
    (12222, 'Biology', 90, 992811191, 'PASSED'),
    (12223, 'Art', 80, 992831192, 'PASSED'),
    (12224, 'History', 70, 928311197, 'FAILED'),
    (12225, 'Pyshics', 75, 928311190, 'FAILED'),
    (12226, 'Art', 85, 928311197, 'PASSED'),
    (12227, 'Biology', 90, 998311196, 'PASSED'),
    (12228, 'Biology', 70, 928311199, 'FAILED'),
    (12229, 'Pyshics', 80, 998311194, 'PASSED'),
    (12231, 'Math', 80, 998311193, 'PASSED'),
    (12232, 'History', 90, 992811191, 'PASSED'),
    (12233, 'Math', 70, 998311194, 'FAILED'),
    (12234, 'Math', 80, 928311190, 'PASSED');
﻿
Display the table
|
SELECT * FROM score;
SELECT * FROM score;
﻿
It will create a table as shown below:
The score table
|
+-----------+----------+------------+------------+-------------+
| score_id  | subject  | score_val  |  stud_id   | score_stat  |
+-----------+----------+------------+------------+-------------+
| 12221     | Math     | 90         | 992811191  | PASSED      |
| 12222     | Biology  | 90         | 992811191  | PASSED      |
| 12223     | Art      | 80         | 992831192  | PASSED      |
| 12224     | History  | 70         | 928311197  | FAILED      |
| 12225     | Pyshics  | 75         | 928311190  | FAILED      |
| 12226     | Art      | 85         | 928311197  | PASSED      |
| 12227     | Biology  | 90         | 998311196  | PASSED      |
| 12228     | Biology  | 70         | 928311199  | FAILED      |
| 12229     | Pyshics  | 80         | 998311194  | PASSED      |
| 12231     | Math     | 80         | 998311193  | PASSED      |
| 12232     | History  | 90         | 992811191  | PASSED      |
| 12233     | Math     | 70         | 998311194  | FAILED      |
| 12234     | Math     | 80         | 928311190  | PASSED      |
+-----------+----------+------------+------------+-------------+
+-----------+----------+------------+------------+-------------+
| score_id  | subject  | score_val  |  stud_id   | score_stat  |
+-----------+----------+------------+------------+-------------+
| 12221     | Math     | 90         | 992811191  | PASSED      |
| 12222     | Biology  | 90         | 992811191  | PASSED      |
| 12223     | Art      | 80         | 992831192  | PASSED      |
| 12224     | History  | 70         | 928311197  | FAILED      |
| 12225     | Pyshics  | 75         | 928311190  | FAILED      |
| 12226     | Art      | 85         | 928311197  | PASSED      |
| 12227     | Biology  | 90         | 998311196  | PASSED      |
| 12228     | Biology  | 70         | 928311199  | FAILED      |
| 12229     | Pyshics  | 80         | 998311194  | PASSED      |
| 12231     | Math     | 80         | 998311193  | PASSED      |
| 12232     | History  | 90         | 992811191  | PASSED      |
| 12233     | Math     | 70         | 998311194  | FAILED      |
| 12234     | Math     | 80         | 928311190  | PASSED      |
+-----------+----------+------------+------------+-------------+
﻿
#Case 1: HAVING Clause with AVG FunctionThe following example uses an AVG aggregate function to filter the student ID with the subject which has an average score of more than 80:
Having clause
|
SELECT subject
FROM score
GROUP BY subject
HAVING AVG (score_val) > 80;
SELECT subject
FROM score
GROUP BY subject
HAVING AVG (score_val) > 80;
﻿
The above query will give the following result:
Output
|
+-----------+
| subject   |
+-----------+
| Art       |
| Biology   |
+-----------+
+-----------+
| subject   |
+-----------+
| Art       |
| Biology   |
+-----------+
﻿
#Case 2: HAVING Clause with COUNT FunctionThe following query lists the number of score statuses that have more than 2 “PASSED” values:
Having clause
|
SELECT COUNT(score_id), subject
FROM score
GROUP BY subject
HAVING COUNT(score_stat = 'PASSED') > 2;
SELECT COUNT(score_id), subject
FROM score
GROUP BY subject
HAVING COUNT(score_stat = 'PASSED') > 2;
﻿
The above query will show that Math and Biology have more than 2 “PASSED” values:
Output
|
+--------+--------------+
| count  | subject      |
+--------+--------------+
| 4      | Math         |
| 3      | Biology      |
+--------+--------------+
+--------+--------------+
| count  | subject      |
+--------+--------------+
| 4      | Math         |
| 3      | Biology      |
+--------+--------------+
﻿
#Case 3: HAVING Clause with MAX FunctionLet’s assume that the minimum score criteria is 75.
Here we will find the maximum score of each subject with the condition that it should be more than 75.
Having clause
|
SELECT subject, MAX(score_val)
FROM score
GROUP BY subject
HAVING MAX(score_val)>75;
SELECT subject, MAX(score_val)
FROM score
GROUP BY subject
HAVING MAX(score_val)>75;
﻿
The returned result will have the maximum score of each subject, as shown below:
Output
|
+-----------+--------+
| subject   | max    |
+-----------+--------+
| Math      | 90     |
| History   | 90     |
| Physics   | 80     |
| Art       | 85     |
| Biology   | 90     |
+-----------+--------+
+-----------+--------+
| subject   | max    |
+-----------+--------+
| Math      | 90     |
| History   | 90     |
| Physics   | 80     |
| Art       | 85     |
| Biology   | 90     |
+-----------+--------+
﻿
#Case 4: HAVING with JOIN ConditionAssume that you want to know which students have failed in their subject.
You can combine the student table with the score table using the JOIN clause and apply a condition on the score_stat column where the values should be equal to FAILED, as shown in the following query:
Having with Join
|
SELECT stud_name, subject, score_val, score_stat
FROM student A
JOIN score C ON A.stud_id = C.stud_id
GROUP BY stud_name, subject, score_val, score_stat
HAVING score_stat = 'FAILED';
SELECT stud_name, subject, score_val, score_stat
FROM student A
JOIN score C ON A.stud_id = C.stud_id
GROUP BY stud_name, subject, score_val, score_stat
HAVING score_stat = 'FAILED';
﻿
The JOIN clause will combine the two tables.
Then, the GROUP BY clause will filter all records from both tables based on the specified columns.
The HAVING clause, then, will filter the records returned from the GROUP BY clause according to the specified condition.
It will deliver the successful result as shown below:
Output
|
+------------+------------+------------+--------------+
| stud_name  | subject    | score_val  | score_stat   |
+------------+------------+------------+--------------+
| Kate       | History    | 70         | FAILED       |
| Sully      | Biology    | 70         | FAILED       |
| Tom        | Physics    | 75         | FAILED       |
| William    | Math       | 70         | FAILED       |
+------------+------------+------------+--------------+
+------------+------------+------------+--------------+
| stud_name  | subject    | score_val  | score_stat   |
+------------+------------+------------+--------------+
| Kate       | History    | 70         | FAILED       |
| Sully      | Biology    | 70         | FAILED       |
| Tom        | Physics    | 75         | FAILED       |
| William    | Math       | 70         | FAILED       |
+------------+------------+------------+--------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
GROUP BY
ORDER BY
Docs powered by archbee
TABLE OF CONTENTS
Overview
Differences Between WHERE and HAVING Clause
Syntax
Examples
#Case 1: HAVING Clause with AVG Function
#Case 2: HAVING Clause with COUNT Function
#Case 3: HAVING Clause with MAX Function
#Case 4: HAVING with JOIN Condition
WHERE	HAVING
The GROUP BY clause appears after the WHERE clause.	The GROUP BY clause appears before the HAVING clause.
The WHERE clause can’t work with an aggregate function.	The HAVING clause can work with an aggregate function.
The WHERE clause filters particular records.	The HAVING clause filters the group of records.