# Source: https://docs.oxla.com/sql-reference/sql-clauses/having.md

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# HAVING

## Overview

The `HAVING` clause specifies a search condition by using an [aggregate function](/sql-reference/sql-functions/aggregate-functions/overview).
It will filter out the records returned from a `GROUP BY` clause that do not fulfill a specified condition.

### Differences Between WHERE and HAVING Clause

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

## Syntax

The basic syntax of the `GROUP BY` clause is as follows:

```sql  theme={null}
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.

## Examples

Let’s assume that we have two tables here, the student table and the score table:

**student table**

```sql  theme={null}
CREATE TABLE student (
  stud_id int,
  stud_name text
);
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');
```

```sql  theme={null}
SELECT * FROM student;
```

It will create a table as shown below:

```sql  theme={null}
+------------+------------+
| stud_id    | stud_name  |
+------------+------------+
| 992831192  | Mary       |
| 992811191  | Bobby      |
| 992311195  | Sean       |
| 998311193  | Harry      |
| 998311194  | William    |
| 928311197  | Kate       |
| 928311190  | Tom        |
| 928311199  | Sully      |
| 998311196  | Susan      |
+------------+------------+
```

**score table**

```sql  theme={null}
CREATE TABLE score (
  score_id int,
  subject text,
  score_val int,
  stud_id int,
  score_stat text
);
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');
```

```sql  theme={null}
SELECT * FROM score;
```

It will create a table as shown below:

```sql  theme={null}
+-----------+----------+------------+------------+-------------+
| 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` Function

The following example uses an `AVG` aggregate function to filter the student ID with the subject which has an average score of more than 80:

```sql  theme={null}
SELECT subject
FROM score
GROUP BY subject
HAVING AVG (score_val) > 80;
```

The above query will give the following result:

```sql  theme={null}
+-----------+
| subject   |
+-----------+
| Art       |
| Biology   |
+-----------+
```

### #Case 2: `HAVING` Clause with `COUNT` Function

The following query lists the number of score statuses that have more than 2 “**PASSED**” values:

```sql  theme={null}
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:

```sql  theme={null}
+--------+--------------+
| count  | subject      |
+--------+--------------+
| 4      | Math         |
| 3      | Biology      |
+--------+--------------+
```

### #Case 3: `HAVING` Clause with `MAX` Function

Let’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**.

```sql  theme={null}
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:

```sql  theme={null}
+-----------+--------+
| subject   | max    |
+-----------+--------+
| Math      | 90     |
| History   | 90     |
| Physics   | 80     |
| Art       | 85     |
| Biology   | 90     |
+-----------+--------+
```

### #Case 4: `HAVING` with `JOIN` Condition

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

```sql  theme={null}
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:

```sql  theme={null}
+------------+------------+------------+--------------+
| stud_name  | subject    | score_val  | score_stat   |
+------------+------------+------------+--------------+
| Kate       | History    | 70         | FAILED       |
| Sully      | Biology    | 70         | FAILED       |
| Tom        | Physics    | 75         | FAILED       |
| William    | Math       | 70         | FAILED       |
+------------+------------+------------+--------------+
```