Aggregation condition – HAVING

Since Hive 0.7.0, HAVING is added to support the conditional filtering of GROUP BY results. By using HAVING, we can avoid using a subquery after GROUP BY. The following is an example:

jdbc:hive2://> SELECT sex_age.age FROM employee 
. . . . . . .> GROUP BY sex_age.age HAVING count(*)<=1;
+--------------+
| sex_age.age  |
+--------------+
| 57           |
| 27           |
| 35           |
+--------------+
3 rows selected (74.376 seconds)

If we do not use HAVING, we can use a subquery for instance as follows:

jdbc:hive2://> SELECT a.age
. . . . . . .> FROM
. . . . . . .> (SELECT count(*) as cnt, sex_age.age 
. . . . . . .> FROM employee GROUP BY sex_age.age
. . . . . . .> ) a WHERE a.cnt<=1;
+--------+
| a.age  |
+--------+
| 57     |
| 27     |
| 35     |
+--------+
3 rows selected (87.298 seconds)