- Impala Tutorial
- Impala - Home
- Impala - Overview
- Impala - Environment
- Impala - Architecture
- Impala - Shell
- Impala - Query Language Basics
- Database Specific Statements
- Impala - Create a Database
- Impala - Drop a Database
- Impala - Select a Database
- Table Specific Statements
- Impala - Create Table Statement
- Impala - Insert Statement
- Impala - Select Statement
- Impala - Describe Statement
- Impala - Alter Table
- Impala - Drop a Table
- Impala - Truncate a Table
- Impala - Show Tables
- Impala - Create View
- Impala - Alter View
- Impala - Drop a View
- Impala - Clauses
- Impala - Order By Clause
- Impala - Group By Clause
- Impala - Having Clause
- Impala - Limit Clause
- Impala - Offset Clause
- Impala - Union Clause
- Impala - With Clause
- Impala - Distinct Operator
- Impala Useful Resources
- Impala - Quick Guide
- Impala - Useful Resources
- Impala - Discussion
Impala - Group By Clause
The Impala GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
Syntax
Following is the syntax of the GROUP BY clause.
select data from table_name Group BY col_name;
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 | +----+----------+-----+-----------+--------+ Fetched 6 row(s) in 0.51s
You can get the total amount of salary of each customer using GROUP BY query as shown below.
[quickstart.cloudera:21000] > Select name, sum(salary) from customers Group BY name;
On executing, the above query gives the following output.
Query: select name, sum(salary) from customers Group BY name +----------+-------------+ | name | sum(salary) | +----------+-------------+ | Ramesh | 20000 | | Komal | 32000 | | Hardik | 40000 | | Khilan | 15000 | | Chaitali | 35000 | | kaushik | 30000 | +----------+-------------+ Fetched 6 row(s) in 1.75s
Assume that this table has multiple records as shown below.
+----+----------+-----+-----------+--------+ | id | name | age | address | salary | +----+----------+-----+-----------+--------+ | 1 | Ramesh | 32 | Ahmedabad | 20000 | | 2 | Ramesh | 32 | Ahmedabad | 1000| | | 3 | Khilan | 25 | Delhi | 15000 | | 4 | kaushik | 23 | Kota | 30000 | | 5 | Chaitali | 25 | Mumbai | 35000 | | 6 | Chaitali | 25 | Mumbai | 2000 | | 7 | Hardik | 27 | Bhopal | 40000 | | 8 | Komal | 22 | MP | 32000 | +----+----------+-----+-----------+--------+
Now again, you can get the total amount of salaries of the employees, considering the repeated entries of records, using the Group By clause as shown below.
Select name, sum(salary) from customers Group BY name;
On executing, the above query gives the following output.
Query: select name, sum(salary) from customers Group BY name +----------+-------------+ | name | sum(salary) | +----------+-------------+ | Ramesh | 21000 | | Komal | 32000 | | Hardik | 40000 | | Khilan | 15000 | | Chaitali | 37000 | | kaushik | 30000 | +----------+-------------+ Fetched 6 row(s) in 1.75s
Advertisements
To Continue Learning Please Login
Login with Google