EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial GROUP BY clause in SQL

GROUP BY clause in SQL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 17, 2023

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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 [email protected]
May 22 456 Female [email protected]
Ana 45 644 Female [email protected]
Pots 57 3456 Female [email protected]
Res 45 456 Female [email protected]
Tan 78 2456 Male [email protected]
Ran 34 5899 Male [email protected]
Wan 55 7789 Female [email protected]
Tung 32 5689 Male [email protected]
Chung 21 4678 Male [email protected]

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 [email protected]
Ana 45 644 Female [email protected]
Pots 57 3456 Female [email protected]
Res 45 456 Female [email protected]
Wan 55 7789 Female [email protected]

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

We hope that this EDUCBA information on “GROUP BY clause in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Insert Query
  2. SQL Views
  3. Database in SQL​ 
  4. Transactions in SQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Software Development Bundle5000+ Hours of HD Videos | 149 Learning Paths | 1050+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more