---
schema_version: "1"
id: 6eb2d12a08314d22fd5da8dd0c1f2756e7fb382238f1b533b20b423c7363cff0
title: HAVING Clause
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_sql/guides/bi-sql-having.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-sql/guide/bi-sql-having.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 2edef15080ec4f71ae6ce5b66aa7ac56e0677e6d96bcdb7e7000de087faa1e10
products:
  - SQL for Analytics Developer Guide
  - analytics
---

# HAVING Clause

Use the `HAVING` clause to filter grouped results from grouped columns, aggregate functions, or grouping functions.

`HAVING` follows this syntax: `HAVING {search-condition}`.

A search condition can contain boolean expressions, comparison operators, and scalar functions.

## Grouped Columns

This query returns groups of cities that contain furniture stores and their counts. The `HAVING` clause filters for the `Furniture` category.

```nolang
SELECT Category, City, count(*) as "cnt" FROM "Superstore" GROUP BY Category, City HAVING Category = 'Furniture' LIMIT 10
```

| Category  | City         | cnt |
| --------- | ------------ | --- |
| Furniture | Akron        | 2   |
|           | Alexandria   | 3   |
|           | Allen        | 1   |
|           | Allentown    | 1   |
|           | Amarillo     | 4   |
|           | Anaheim      | 8   |
|           | Andover      | 1   |
|           | Apopka       | 1   |
|           | Apple Valley | 2   |
|           | Arlington    | 13  |

You can also write this query using `WHERE`.

```nolang
SELECT Category, City, count(*) as "cnt" FROM "Superstore" WHERE Category = 'Furniture' GROUP BY Category, City LIMIT 10
```

In the logical execution of a `SELECT` statement, `WHERE` filters out rows before any grouping or aggregate function runs. In this example, it selects the rows that can be passed to the aggregate function `count()`. The `WHERE` clause comes before a `GROUP BY` statement. With `HAVING`, the filtering occurs after all of the rows have been passed to `count()`. `HAVING` always comes after a `GROUP BY` statement. Both `HAVING` and `WHERE` run before projection.

## Aggregate Functions

This query returns groups of cities that have more than 150 stores in each category. The `HAVING` clause filters out values greater than 150 in the aggregate column, `cnt`.

```nolang
SELECT Category, City, count(*) as cnt FROM "Superstore"
GROUP BY Category, City HAVING count(*) > 150
```

| Category        | City          | cnt |
| --------------- | ------------- | --- |
| Furniture       | Los Angeles   | 154 |
|                 | New York City | 192 |
| Office Supplies | Houston       | 231 |
|                 | Los Angeles   | 443 |
|                 | New York City | 552 |
|                 | Philadelphia  | 312 |
|                 | San Francisco | 322 |
|                 | Seattle       | 249 |
| Technology      | New York City | 171 |

Using `HAVING` with an aggregate function automatically implies `GROUP BY()`. These two queries return the same result.

```nolang
SELECT Sum(Profit) as TotalProfit
FROM "Superstore"
HAVING Sum(Profit) > 0
```

```nolang
SELECT Sum(Profit) as TotalProfit
FROM "Superstore"
GROUP BY ()
HAVING Sum(Profit) > 0
```

| TotalProfit |
| ----------- |
| 286,397.02  |

## GROUP BY ROLLUP

Here’s a simple example of how to use `HAVING` with `GROUP BY ROLLUP`. The query returns the profit for each `Sub-Category`, rolls them up into subtotals of each `Category`, and then sums up the grand total. Here, `HAVING` filters on positive profits.

```nolang
SELECT Category, Sub_Category, Sum(Profit) as TotalProfit FROM "Superstore" GROUP BY ROLLUP(Category, Sub_Category) HAVING Sum(Profit) > 0;
```

| Category        | Sub-Category | TotalProfit |
| --------------- | ------------ | ----------- |
| Furniture       | Chairs       | 26,590.17   |
|                 | Furnishings  | 13,059.14   |
|                 | -            | 18,451.27   |
| Office Supplies | Appliances   | 18,138.01   |
|                 | Art          | 6,527.79    |
|                 | Binders      | 30,221.76   |
|                 | Envelopes    | 6,964.18    |
|                 | Fasteners    | 949.52      |
|                 | Labels       | 5,546.25    |
|                 | Paper        | 34,053.57   |
|                 | Storage      | 21,278.83   |
|                 | -            | 122,490.8   |
| Technology      | Accessories  | 41,936.64   |
|                 | Copiers      | 55,617.82   |
|                 | Machines     | 3,384.76    |
|                 | Phones       | 44,515.73   |
|                 | -            | 145,454.95  |
|                 | -            | 286,397.02  |

## GROUP BY ROLLUP with GROUPING()

In SQL for CRM Analytics, you can use `HAVING()` with the `GROUP BY ROLLUP` subclause and the `GROUPING()` function. You can use `GROUPING()` with `ROLLUP` only; you can't use it on its own.

This example uses the `GROUPING()` function with the `HAVING()` function to filter for the subtotals of each category and return the grand total. The `GROUPING()` function returns 1 for null values that are the result of a `ROLLUP`, and not null values in your dataset. Here, by setting `GROUPING(Sub_Category) = 1`, we know that these `Sub-Category` values refer to the subtotals of each category.

```nolang
SELECT Category, Sub_Category, Sum(Profit) as TotalProfit
FROM "Superstore"
GROUP BY ROLLUP(Category, Sub_Category)
HAVING GROUPING(Sub_Category) = 1
```

| Category        | Sub-Category | TotalProfit |
| --------------- | ------------ | ----------- |
| Furniture       | -            | 18,451.27   |
| Office Supplies | -            | 122,490.8   |
| Technology      | -            | 145,454.95  |
| -               | -            | 286,397.02  |