Updated March 17, 2023
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 | |
| 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 | |
| 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.
