Impala GROUP BY Clause – Zero-Length Strings

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. Objective

It is possible to arrange identical data into groups in Impala. For that, we use Impala Group BY clause. So, in this article, we will discuss, how we can arrange data with Impala Group BY clause. Also, we will cover its syntax, usage as well as an example to understand it well.
So, let’s start Impala Group by Clause.

Impala GROUP BY Clause - Zero-Length Strings

Impala GROUP BY Clause – Zero-Length Strings

2. Impala GROUP BY Clause

Basically, Impala GROUP BY Clause specifies the GROUP BY clause in queries that use aggregation functions. Like COUNT(), SUM(), AVG(), MIN(), and MAX(). Moreover, specify the names of all the columns that do not participate in the aggregation operation in the GROUP BY clause.
In other words, to arrange identical data into groups, we use the Impala GROUP BY clause in collaboration with SELECT statement.
Let’s Learn Impala ORDER BY Clause

3. Syntax of Group By Clause in Impala

Here, is the syntax of Impala GROUP BY clause, below;

select data from table_name Group BY col_name;

4. Complex Type Considerations

There are several complex data types available in CDH 5.5 / Impala 2.3 and higher. Like STRUCT, ARRAY, and MAP.
Basically, we cannot reference these columns directly in the ORDER BY clause. So, we use join notation to “unpack” the elements of the complex type when we query a complex type column. Moreover, we can include an ORDER BY clause to control the order in the result set of the scalar elements from the complex type within the join query.

5. Zero-Length Strings in Group By

Impala considers zero-length strings (“”), NULL, and space to all the different values for purposes of several clauses. Like DISTINCT and GROUP BY.

Read about Impala LIMIT Clause with syntax

6. Examples of Impala Group By Clause

Let’s Understand by Impala Group By example,

Let us suppose we have a table named Employees in the database my_db. Its contents are −

[quickstart.cloudera:21000] > select * from Employees;
Query: select * from Employees
id name age address salary
1 shubham 32 delhi 20000
2 monika 25 mumbai 15000
3 kajal 27 alirajpur 40000
4 revti 25 indore 35000
5 shreyash 23 pune 30000
6 mehul 22 hyderabad 32000

Fetched 6 row(s) in 0.51s
Further, using GROUP BY query, we can get the total amount of salary of each Employee.

[quickstart.cloudera:21000] > Select name, sum(salary) from Employees Group BY name;

On executing, the above query we get the following output.
Read about Impala UNION Clause with Example
Query: select name, sum(salary) from Employees Group BY name

name   sum(salary)
shubham 20000
mehul 32000
Shreyash 30000
monika 15000
revti 35000
kajal 40000

Fetched 6 row(s) in 1.75s
However, now suppose that this table has multiple records.

id name age address salary
1 shubham 32 delhi 20000
2 shubham 32 delhi 2000
3 monika 25 mumbai 15000
4 kajal 27 alirajpur 40000
5 revti 25 indore 35000
6 revti 25 indore 3000
7 shreyash 23 pune 30000
8 mehul 22 hyderabad 32000

So, using the Group By clause again, we can get the total amount of salaries of the employees, considering the repeated entries of records.

Select name, sum(salary) from Employees Group BY name;

So, above query gives the following output while execution.
Do you know about Impala OFFSET Clause with Example

Query: select name, sum(salary) from Employees Group BY name 

name   sum(salary)
shubham 22000
mehul 32000
Shreyash 30000
monika 15000
revti 38000
kajal 40000

Fetched 6 row(s) in 1.75s

So, this was all in the Impala Group By Clause tutorial. Hope you like our explanation.

7. Conclusion

As a result, we have seen the whole concept of Impala GROUP BY Clause. Still, if any doubt occurs in how to arrange data in groups, feel free to ask in the comment section.
See Also- Impala DISTINCT Operator
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.