Having Clause#

Having clause is similar to the Where clause. The Having clause filters data after GroupBy, and the Where clause is used to filter records before aggregation.

Syntax#

HavingClause
         ::= 'HAVING' Expression

SQL Statement Template#

SELECT select_expr [,select_expr...] FROM ... GROUP BY ... HAVING having_condition

Description#

For the standalone version, HAVING is supported in all conditions. For the cluster version, the execution modes, which support this clause, are shown below

SELECT Statement Elements

Offline Mode

Online Preview Mode

Online Request Mode

Note

HAVING Clause

✓

✓(since 0.6.4)

The Having clause is similar to the Where clause. The Having clause filters data after GroupBy, and the Where clause is used to filter records before aggregation.

Example#

1. Filter By Aggregation Results After Grouping

SELECT COL1, SUM(COL2), AVG(COL2) FROM t1 group by COL1 HAVING SUM(COL2) > 1000;

2. Filter By Aggregation Result After Grouping By Two Columns

SELECT COL1, SUM(COL2), AVG(COL2) FROM t1 group by COL1, COL0 HAVING SUM(COL2) > 1000;

3. Filter By Grouping Column After Grouping

SELECT COL1, SUM(COL2), AVG(COL2) FROM t1 group by COL1 HAVING COL1 ='a';