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.

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

You can also write this query using WHERE.

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.

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

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

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

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

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.

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

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.

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