Using HAVING in GROUP BY

Function

This statement filters a table after grouping it using the HAVING clause.

Syntax

1
2
3

SELECT attr_expr_list FROM table_reference
  GROUP BY groupby_expression [, groupby_expression...]
  HAVING having_expression;

Keyword

The groupby_expression can contain a single field or multiple fields, and can also call aggregate functions or string functions.

Precautions

• The to-be-grouped table must exist. Otherwise, an error is reported.
• If the filtering condition is subject to the query results of GROUP BY, the HAVING clause, rather than the WHERE clause, must be used for filtering. If HAVING and GROUP BY are used together, GROUP BY applies first for grouping and HAVING then applies for filtering. The arithmetic operation and aggregate function are supported by the HAVING clause.

Example

Group the transactions according to num, use the HAVING clause to filter the records in which the maximum value derived from multiplying price with amount is higher than 5000, and return the filtered results.

1
2
3
4

SELECT num, max(price*amount) FROM transactions
  WHERE time > '2016-06-01'
  GROUP BY num
  HAVING max(price*amount)>5000;