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
conditionA comparison on an aggregate such as
Required: At least oneCOUNT(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
Supported operators
A condition compares an aggregate with a number. Each row shows a condition and what it keeps.
| Operator | Example | Meaning |
|---|---|---|
= | COUNT(b.id) = 1 | Keeps only the kinds that have exactly one entry |
!= | COUNT(b.id) != 1 | Leaves out the kinds that have just one entry, so every remaining kind has two or more |
<> | AVERAGE(b.version) <> 1 | Keeps the kinds whose average version is anything but 1, the ones that have been revised |
> | COUNT(b.id) > 100 | Keeps the busiest kinds, those with more than a hundred entries |
>= | SUM(b.version) >= 46 | Keeps the kinds whose versions add up to 46 or more, so groups with a long edit history stay |
< | COUNT(b.id) < 5 | Keeps the small kinds, those with fewer than five entries |
<= | MAX(b.version) <= 1 | Keeps 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
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 | The condition is not written in HTQL's grammar | Write 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.