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.

You can also write this query using WHERE.

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.

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

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.

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.