HAVING Clause Overview for Grouped Data Filtering - AnalyticDB for MySQL

Use the HAVING clause to filter groups produced by GROUP BY and an aggregate function. Unlike the WHERE clause, which filters individual rows before grouping, HAVING filters groups after aggregation.

[ HAVING condition ]

Usage notes

The column referenced in HAVING must either be a grouping column or appear inside an aggregate function.
HAVING must be used together with GROUP BY and at least one aggregate function.

Examples

Filter groups by aggregate total

Group the customer table by market segment and nation, then return only the groups whose total account balance exceeds 5,700,000.

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;

Output:

 _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