Since v0.7.0, HAVING has been added to support the conditional filtering of aggregation results directly. By using HAVING, we can avoid using a subquery after the GROUP BY statement. See the following example:
> SELECT
> gender_age.age
> FROM employee
> GROUP BY gender_age.age
> HAVING count(*)=1;
+----------------+
| gender_age.age |
+----------------+
| 27 |
| 30 |
| 35 |
| 57 |
+----------------+
4 rows selected (25.829 seconds)
> SELECT
> gender_age.age,
> count(*) as cnt -- Support use column alias in HAVING, like ORDER BY
> FROM employee
> GROUP BY gender_age.age HAVING cnt=1;
+----------------+-----+
| gender_age.age | cnt |
+----------------+-----+
| 27 | 1 |
| 30 | 1 |
| 35 | 1 |
| 57 | 1 |
+----------------+-----+
4 rows selected (25.804 seconds)
HAVING supports filtering on regular columns too. However, it is recommended to use such a filter type after a WHERE clause rather than HAVING for better performance.
If we do not use HAVING, we can use a subquery instead as follows:
> SELECT
> a.age
> FROM (
> SELECT count(*) as cnt, gender_age.age
> FROM employee GROUP BY gender_age.age
> ) a WHERE a.cnt <= 1;
+--------+
| a.age |
+--------+
| 57 |
| 27 |
| 35 |
+--------+
3 rows selected (87.298 seconds)