Syntax of the HAVING clause - AnalyticDB - Alibaba Cloud Documentation Center

You can use the HAVING clause to filter the data that is grouped and aggregated by using the GROUP BY clause and an aggregate function. The HAVING clause must be used together with an aggregate function and the GROUP BY clause. After grouping and aggregation, the system filters out the groups that do not meet the conditions. This topic describes the HAVING clause syntax and provides examples on how to use the HAVING clause.

[ HAVING condition ]

Usage notes

The column that you reference in the HAVING clause must be used for grouping or referenced in an aggregate function.
The HAVING clause must be used together with an aggregate function and the GROUP BY clause to filter out the groups that do not meet the conditions.

Example

Group the data in the customer table and query the data 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