PostgreSQL Having Clause

HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function.

The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true.

The having clause is a =n additional filter that is applied to the the result set. Logically, the HAVING clause filters rows from the intermediate result built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. Having clauses typically used with GROUP BY clause.

Syntax:

SELECT select_list FROM … [WHERE …] GROUP BY … HAVING boolean_expression

Examples:

SELECT x, sum(y) FROM test GROUP BY x HAVING sum(y) > 3; x | sum ---+----- b | 8 a | 5 (2 rows)

Powered by k2schools