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.

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

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

You can also write this query using WHERE.

1SELECT 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.

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.

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

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

1SELECT Sum(Profit) as TotalProfit
2FROM "Superstore"
3HAVING Sum(Profit) > 0

1SELECT Sum(Profit) as TotalProfit
2FROM "Superstore"
3GROUP BY ()
4HAVING Sum(Profit) > 0

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.

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

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.

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

j

Introducing SQL for CRM Analytics

Quick Start: Run Your First Query

SQL vs SAQL

SQL Statements

SELECT Clause

FROM Clause

WHERE Clause

Boolean Expressions in SELECT Clause

GROUP BY Clause

GROUP BY ROLLUP

GROUPING()

HAVING Clause

ORDER BY Clause

LIKE Clause

BETWEEN Operator

LIMIT Clause

FETCH Clause

OFFSET Clause

CASE Statements

COALESCE()

NULLIF()

UNION Operator

Subquery

Window Functions

Date Functions

Aggregate Functions

Math Functions

String Functions and Operators

Release Notes

Previous Versions