HAVING

HAVING allows filtering of rows that were created using GROUP BY.

Syntax

SELECT ... 
FROM ... 
GROUP BY ... 
HAVING predicate
[ ... ] 
predicate

Parameters

predicate: A boolean expression.

Usage Notes

• The condition specified in the HAVING clause applies to expressions produced by GROUP BY. Therefore, the same restrictions that apply to GROUP BY expressions also apply to HAVING.

• The predicate may refer only to:

  • Constants
  • Expressions listed in the GROUP BY clause
  • Aggregate functions

• Expressions in the SELECT list can be referenced by the column alias defined in that list.

Example 1

WITH
t1 AS (
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 1 AS col1, 3 AS col2
  UNION ALL 
  SELECT 2 AS col1, 4 AS col2
  UNION ALL 
  SELECT 2 AS col1, 5 AS col2
  UNION ALL 
  SELECT 3 AS col1, 6 AS col2
)
SELECT col1, SUM(col2) AS sum_col2
  FROM t1
  GROUP BY col1
  HAVING SUM(col2) < 9;

+------+----------+
| COL1 | SUM_COL2 |
+------+----------+
| 1    | 5        |
| 3    | 6        |
+------+----------+

Example 2

WITH
t1 AS (
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 1 AS col1, 3 AS col2
  UNION ALL 
  SELECT 2 AS col1, 4 AS col2
  UNION ALL 
  SELECT 2 AS col1, 5 AS col2
  UNION ALL 
  SELECT 3 AS col1, 6 AS col2
)
SELECT col1, SUM(col2) AS sum_col2
  FROM t1
  GROUP BY col1
  HAVING COUNT(*) > 1;

+------+----------+
| COL1 | SUM_COL2 |
+------+----------+
| 1    | 5        |
| 2    | 9        |
+------+----------+