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 of the Syntax 

1. SELECT statement – SQL command query
2. table_name – the name of the database table being referred to
3. conditions – refer to the conditions to be checked
4. GROUP BY – clause to group the same rows together
5. 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

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 –

SELECT gender FROM EMPLOYEE;

This will give us the following values-

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 –

SELECT gender FROM EMPLOYEE GROUP BY gender;

The result we receive after executing this query will be –

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

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

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

The execution of this query fetches the following result –

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.

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

The execution of this query fetches the following result –

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 –

Example #3

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

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 –

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.

Understanding this with an example will make it easier –

Example #4

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

The execution of this query fetches the following result –

Example #5

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

The execution of this query fetches the following result –

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