# HAVING

> For the complete documentation index, see [llms.txt](https://docs.redpanda.com/llms.txt). Component-specific: [cloud-data-platform-full.txt](https://docs.redpanda.com/cloud-data-platform-full.txt)

---
title: HAVING
latest-operator-version: v26.1.4
latest-console-tag: v3.7.3
latest-connect-version: 4.93.0
latest-redpanda-tag: v26.1.9
docname: sql/sql-clauses/having
page-component-name: cloud-data-platform
page-version: master
page-component-version: master
page-component-title: Cloud
page-relative-src-path: sql/sql-clauses/having.adoc
page-edit-url: https://github.com/redpanda-data/cloud-docs/edit/main/modules/reference/pages/sql/sql-clauses/having.adoc
description: The HAVING clause specifies a search condition using an aggregate function.
page-topic-type: reference
page-git-created-date: "2026-05-26"
page-git-modified-date: "2026-05-26"
---

<!-- Source: https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-clauses/having.md -->

The `HAVING` clause specifies a search condition using an [aggregate function](https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-functions/aggregate-functions/). It filters the records returned from a `GROUP BY` clause that do not fulfill the specified condition.

## [](#differences-between-where-and-having)Differences between WHERE and HAVING

| WHERE | HAVING |
| --- | --- |
| The GROUP BY clause appears after the WHERE clause. | The GROUP BY clause appears before the HAVING clause. |
| The WHERE clause cannot work with an aggregate function. | The HAVING clause can work with an aggregate function. |
| The WHERE clause filters individual records. | The HAVING clause filters groups of records. |

## [](#syntax)Syntax

```sql
SELECT column_1, column_2,...
FROM table_name
GROUP BY column_name(s)
HAVING condition_aggregate_function
```

This syntax includes the following elements:

-   `SELECT column_1, column_2,…​` selects the columns to display.

-   `FROM table_name` selects the table to retrieve data from.

-   `GROUP BY column_name(s)` lists the columns to group.

-   `HAVING condition_aggregate_function` provides the condition for filtering rows formed by the `GROUP BY` clause. The condition can use an aggregate function such as `sum()`, `count()`, or `min()`.


## [](#examples)Examples

Assume there are two tables: `student` and `score`.

Create the `student` table:

```sql
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
SELECT * FROM student;
```

This returns:

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

Create the `score` table:

```sql
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, 'Physics', 75, 928311190, 'FAILED'),
    (12226, 'Art', 85, 928311197, 'PASSED'),
    (12227, 'Biology', 90, 998311196, 'PASSED'),
    (12228, 'Biology', 70, 928311199, 'FAILED'),
    (12229, 'Physics', 80, 998311194, 'PASSED'),
    (12231, 'Math', 80, 998311193, 'PASSED'),
    (12232, 'History', 90, 992811191, 'PASSED'),
    (12233, 'Math', 70, 998311194, 'FAILED'),
    (12234, 'Math', 80, 928311190, 'PASSED');
```

```sql
SELECT * FROM score;
```

This returns:

```sql
+-----------+----------+------------+------------+-------------+
| 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     | Physics  | 75         | 928311190  | FAILED      |
| 12226     | Art      | 85         | 928311197  | PASSED      |
| 12227     | Biology  | 90         | 998311196  | PASSED      |
| 12228     | Biology  | 70         | 928311199  | FAILED      |
| 12229     | Physics  | 80         | 998311194  | PASSED      |
| 12231     | Math     | 80         | 998311193  | PASSED      |
| 12232     | History  | 90         | 992811191  | PASSED      |
| 12233     | Math     | 70         | 998311194  | FAILED      |
| 12234     | Math     | 80         | 928311190  | PASSED      |
+-----------+----------+------------+------------+-------------+
```

### [](#having-clause-with-avg-function)HAVING clause with AVG function

The following example uses the `avg` aggregate function to filter subjects with an average score greater than 80:

```sql
SELECT subject
FROM score
GROUP BY subject
HAVING AVG (score_val) > 80;
```

The query returns:

```sql
+-----------+
| subject   |
+-----------+
| Art       |
| Biology   |
+-----------+
```

### [](#having-clause-with-count-function)HAVING clause with COUNT function

The following query lists subjects that have more than two `PASSED` scores:

```sql
SELECT SUM(CASE WHEN score_stat = 'PASSED' THEN 1 ELSE 0 END) AS passed_count, subject
FROM score
GROUP BY subject
HAVING SUM(CASE WHEN score_stat = 'PASSED' THEN 1 ELSE 0 END) > 2;
```

The query returns `Math` as the only subject with more than two `PASSED` values:

```sql
+--------------+---------+
| passed_count | subject |
+--------------+---------+
| 3            | Math    |
+--------------+---------+
```

### [](#having-clause-with-max-function)HAVING clause with MAX function

Assume the minimum score criterion is 75. The following query finds the maximum score for each subject where the maximum is greater than 75:

```sql
SELECT subject, MAX(score_val)
FROM score
GROUP BY subject
HAVING MAX(score_val)>75;
```

The query returns:

```sql
+-----------+--------+
| subject   | max    |
+-----------+--------+
| Math      | 90     |
| History   | 90     |
| Physics   | 80     |
| Art       | 85     |
| Biology   | 90     |
+-----------+--------+
```

### [](#having-with-join-condition)HAVING with JOIN condition

To find students who have failed in their subjects, combine the `student` and `score` tables using `JOIN` and apply a condition on the `score_stat` column:

```sql
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 combines the two tables.

-   The `GROUP BY` clause filters records from both tables based on the specified columns.

-   The `HAVING` clause filters the records returned from the `GROUP BY` clause according to the specified condition.


The query returns:

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