HAVING Clause ​

The HAVING clause filters data after grouping.

The WHERE clause sets conditions on selected columns, while the HAVING clause sets conditions on groups created by the GROUP BY clause.

Syntax ​

SELECT 
{ * | [column, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ ORDER BY {expression [ ASC | DESC ] }];

Parameter Description ​

• HAVING clause

  Selects special groups by working with the GROUP BY clause. The HAVING clause compares some attributes of groups with a constant. Only groups that match the logical expression in the HAVING clause are extracted.

Examples ​

In the following example, data is grouped based on the value of c_first_name which is less than 2.

openGauss=# SELECT c_first_name FROM customer_t1 GROUP BY c_first_name HAVING count(c_first_name) < 2;

The result is as follows:

 c_first_name
--------------
 James
 Local

 Joes
(4 rows)

In the following example, data is grouped based on the value of c_first_name which is greater than 1.

openGauss=# SELECT c_first_name FROM customer_t1 GROUP BY c_first_name HAVING count(c_first_name) > 1;

The result is as follows:

 c_first_name
--------------
 Grace
 Lily
(2 rows)