You can use the HAVING clause with the GROUP BY clause and aggregate functions to show only the groups that meet certain conditions after grouping and aggregation.

[ HAVING condition ]     

Precautions

  • The columns that you reference in the HAVING clause must be used for grouping or contain an aggregate function.
  • The HAVING clause must be used together with aggregate functions and the GROUP BY clause to show only the groups that are grouped by the GROUP BY clause and meet certain conditions.

Example

Group records in the customer table and query the records whose account balance is greater than the specified value.

SELECT count(*), mktsegment, nationkey,
       CAST(sum(acctbal) AS bigint) AS totalbal
FROM customer
GROUP BY mktsegment, nationkey
HAVING sum(acctbal) > 5700000
ORDER BY totalbal DESC;

 _col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
  1272 | AUTOMOBILE |        19 |  5856939
  1253 | FURNITURE  |        14 |  5794887
  1248 | FURNITURE  |         9 |  5784628
  1243 | FURNITURE  |        12 |  5757371
  1231 | HOUSEHOLD  |         3 |  5753216
  1251 | MACHINERY  |         2 |  5719140
  1247 | FURNITURE  |         8 |  5701952