Impala - Having Clause

The Having clause in Impala enables you to specify conditions that filter which group results appear in the final results.

In general, the Having clause is used along with group by clause; it places conditions on groups created by the GROUP BY clause.

Syntax

Following is the syntax of the Havingclause.

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-------------+--------+ 
| id | name     | age | address     | salary | 
+----+----------+-----+-------------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad   | 20000  |
| 2  | Khilan   | 25  | Delhi       | 15000  | 
| 3  | kaushik  | 23  | Kota        | 30000  | 
| 4  | Chaitali | 25  | Mumbai      | 35000  | 
| 5  | Hardik   | 27  | Bhopal      | 40000  | 
| 6  | Komal    | 22  | MP          | 32000  | 
| 7  | ram      | 25  | chennai     | 23000  | 
| 8  | rahim    | 22  | vizag       | 31000  | 
| 9  | robert   | 23  | banglore    | 28000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

Following is an example of using Having clause in Impala −

[quickstart.cloudera:21000] > select max(salary) from customers group by age having max(salary) > 20000;

This query initially groups the table by age and selects the maximum salaries of each group and displays those salaries, which are greater than 20000 as shown below.

20000 
+-------------+ 
| max(salary) |
+-------------+ 
| 30000       |
| 35000       | 
| 40000       | 
| 32000       | 
+-------------+ 
Fetched 4 row(s) in 1.30s