GROUP BY clause in SQL

Introduction to GROUP BY clause in SQL

For the cases where we need to group the rows with the same value Group By clause comes into the picture. The GROUP BY clause is a command in SQL that will perform this operation. The important point to remember here is that the GROUP BY clause is used in the SELECT statement in the query after the WHERE clause is used.

Syntax:

The syntax for Group By clause is:

SELECT column_name1,column_name2,...
FROM table_name
WHERE conditions
GROUP By column_name1,column_name2,...
ORDER By column_name1,column_name2,...;

Explanation:

• SELECT statement – SQL command query.
• table_name – The name of the database table being referred to.
• conditions – Refer to the conditions to be checked.
• GROUP BY – Clause to group the same rows together.
• ORDER BY – Always used after Group By clause for arranging the rows in ascending or descending order. This is an optional condition.

How GROUP BY Clause Works in SQL?

In order to understand the working of the Group By clause, let us take the help of an example. Consider a table with a name as EMPLOYEE, which contains the basic details of the employee such as name, age, phone number, dob, gender, address, email id, etc.

Employee Table:

name	age	phone	gender	email
John	23	123	Male	john@email.com
May	22	456	Female	May@email.com
Ana	45	644	Female	Ana@email.com
Pots	57	3456	Female	Pots@email.com
Res	45	456	Female	Res@email.com
Tan	78	2456	Male	Tan@email.com
Ran	34	5899	Male	Ran@email.com
Wan	55	7789	Female	Wan@email.com
Tung	32	5689	Male	Tung@email.com
Chung	21	4678	Male	Chung@email.com

Consider the EMPLOYEE has around 10 entries.

Let us first get the different values of “gender” from this table.

The query that will help us fetch our objective would be:

Code:

SELECT gender FROM EMPLOYEE;

Output:

gender

Male

Female

Female

Female

Female

Male

Male

Female

Male

Male

The problem here is the redundancy of the values, i.e. as we can see, there are just two unique values of genders in the table, but the output does not just give us the unique values but all the values even if they are repetitive.

So, in order to get only the unique values from the table, we will use the following query:

Code:

SELECT gender FROM EMPLOYEE GROUP BY gender;

The result we receive after executing this query will be:

Output:

gender

Male

Female

The Group By will group all the same values in the rows together and return only a single entry or a single row for them as it did with the “Male” and “Female” rows in the table. This resulted in just two unique values that were present in the “gender” column I,e. Female and Male.

Now, let us take another example of a table that contains the department’s details with which each of these employees is associated. We will call this an Employee_Department table.

Employee Department Table:

name	hod	department
John	hod1	Service
May	hod2	Finance
Ana	hod1	Service
Pots	hod2	Finance
Res	hod3	Tech
Tan	hod5	Support
Ran	hod3	Service
Wan	hod3	Tech
Tung	hod3	Finance
Chung	hod5	HR

So now let us execute the below query and look for the result:

Code:

SELECT hod,department FROM Employee_Department GROUP BY hod,department;

The execution of this query fetches the following result:

hod	department
hod1	Service
hod2	Finance
hod3	Tech
hod5	Support
hod3	Service
hod3	Finance
hod5	HR

The GROUP BY clause functions on both the hod and department to look for the unique rows in the above-mentioned scenario. It will check the combination of hod and department against other entries of the hod and department to identify its uniqueness. If the hod is the same, but the department is different, then that row is treated as a unique one. If the hod and the department are the same for more than one row, then the duplicate entry is not created, and only one row is shown.

GROUP BY Clause Operations

We can use Group By clause with several grouping and aggregate functions. Let us take a few examples using the above two mentioned tables, i.e., EMPLOYEE and Employee_Department.

Example #1

Getting the count of male employees and female employees in the company.

Code:

SELECT gender, COUNT(`name`) FROM EMPLOYEE GROUP BY gender;

The execution of this query fetches the following result:

COUNT(‘name’)	gender
5	Male
5	Female

Example #2

Getting the count of departments in the company in descending order of the count.

SELECT department,COUNT(`name`) FROM Employee_Department GROUP BY  department ORDER BY COUNT(name) DESC;

The execution of this query fetches the following result –

COUNT(‘name’)	department
3	Service
3	Finance
2	Tech
1	Support
1	HR

Example #3

Getting the count of different departments under the same hod in the company in descending order of the count.

Code:

SELECT hod, department, COUNT(`name`) FROM Employee_Department GROUP BY hod, department ORDER BY COUNT(name) DESC;

The execution of this query fetches the following result –

COUNT(‘name’)	hod	department
2	hod1	Service
2	hod2	Finance
2	hod3	Tech
1	hod5	Support
1	hod3	Service
1	hod3	Finance
1	hod5	HR

The HAVING Clause in SQL: Using the HAVING clause with the Group By clause will restrict the result of the query to the rows having the value mentioned with the Having clause

Example #4

Code:

SELECT * FROM EMPLOYEE GROUP BY gender HAVING gender = “Female”;

Output:

name	age	phone	gender	email
May	22	456	Female	May@email.com
Ana	45	644	Female	Ana@email.com
Pots	57	3456	Female	Pots@email.com
Res	45	456	Female	Res@email.com
Wan	55	7789	Female	Wan@email.com

Example #5

Code:

SELECT * FROM Employee_Department GROUP BY department HAVING department = “Finance”;

Output:

name	hod	department
May	hod2	Finance
Pots	hod2	Finance
Tung	hod3	Finance

Conclusion

Using the GROUP BY Clause with the SELECT statement, we can group rows with the same values and aggregate functions, constants, and expressions.

Recommended Articles

This is a guide to the GROUP BY clause in SQL. Here we discuss how GROUP BY clause works in SQL with the help of examples and employee tables. You may also have a look at the following articles to learn more –

1. SQL Insert Query
2. SQL Views
3. Database in SQL​ 
4. Transactions in SQL