HAVING

Keep only the groups that matter.

Syntax

HAVING is a clause of LEARN, written after GROUP. Put aggregate conditions inside it.

HAVING(COUNT(alias.column) > 100)
HAVING(COUNT(alias.column) >= 29, COUNT(alias.column) < 100)
HAVING(SUM(alias.column) >= 46, MAX(alias.column) > 1)

Parameters and arguments

  • condition

    A comparison on an aggregate such as COUNT(b.id) > 100. The aggregate may be COUNT, SUM, AVERAGE, MAX or MIN, and need not be in RETURN. Several conditions separated by commas must all hold

    Required: At least one

Supported operators

A condition compares an aggregate with a number. Each row shows a condition and what it keeps.

OperatorExampleMeaning
=COUNT(b.id) = 1Keeps only the kinds that have exactly one entry
!=COUNT(b.id) != 1Leaves out the kinds that have just one entry, so every remaining kind has two or more
<>AVERAGE(b.version) <> 1Keeps the kinds whose average version is anything but 1, the ones that have been revised
>COUNT(b.id) > 100Keeps the busiest kinds, those with more than a hundred entries
>=SUM(b.version) >= 46Keeps the kinds whose versions add up to 46 or more, so groups with a long edit history stay
<COUNT(b.id) < 5Keeps the small kinds, those with fewer than five entries
<=MAX(b.version) <= 1Keeps the kinds where no entry has ever been revised

Examples

The largest groups only.

Return type and behaviour

HAVING removes the groups whose aggregate fails the condition. FILTER keeps rows before they are grouped, and HAVING keeps groups after. If no group passes, the answer is an empty list.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-1003The condition is not written in HTQL's grammarWrite an aggregate, an operator and a number
Limitations
  • HAVING tests aggregates. Use FILTER to test a column.
Best practices
  • Use FILTER for conditions on a column and HAVING for conditions on an aggregate.
  • Sort the groups that remain.