How to Use GROUP BY in SQL

In this SQL section, you will learn what the GROUP BY keyword is and how to implement it in Structured Query Language. We will also discuss how to use GROUP BY clause with WHERE clause.

What is GROUP BY?

GROUP BY is an SQL keyword used in the SELECT query for arranging the same values of a column in the group by using SQL functions.

Syntax of GROUP BY clause

We can use more than one field of the table in the GROUP BY clause. We have to separate the name of multiple columns by a comma.

Follow the steps given below to learn how to use the GROUP BY clause in the SQL table:

1. Create a Simple Database and Table.
2. Insert Data into the Table
3. View the Inserted Data without the GROUP BY clause.
4. Use the GROUP BY clause.

Step 1: Create a simple Database and Table

First, you have to create a new database in SQL.

The following query creates the Hospital Database:

Now, you have to create the new table using the following CREATE TABLE syntax:

The following query creates the Doctor_Info table in the Hospital Database:

Step 2: Insert the Data into the table

Now, you have to insert the data in the table using the following syntax:

The following query inserts the record of those doctors who work in Hospital:

Step 3: View the Inserted data of Table Without GROUP BY

The following query shows the record of Doctors in an unsorted manner:

The output of the above SELECT query is shown below:

Step 4: Use GROUP BY clause

The following SQL query uses the GROUP BY keyword to list the number of doctors in each country:

The output of the above SELECT with ODER BY query is shown in the following Doctor table:

Output:

GROUP BY with SQL ORDER BY clause

We can also use the ORDER BY keyword with the GROUP BY clause in the SQL SELECT statement.

Syntax of GROUP BY clause with ORDER BY clause

Example of GROUP BY clause with ORDER BY clause

The following query creates the new Subject table in the School database:

The following INSERT INTO query inserts the records into the Subject table:

The following query uses the SQL ORDER BY clause with GROUP BY:

Output:

GROUP BY clause with MIN function

We can also use the MIN aggregate function with the GROUP BY clause in Structured Query Language.

Syntax of Group BY clause with MIN function:

Example of MIN Aggregate Function with GROUP BY Clause

This example takes the below School_Stu_Details table to understand the concept of GROUP BY clause with MIN aggregate function:

The following INSERT INTO statements insert the record of School students:

The following query simply shows the record of students in the tabular form:

The following query shows the minimum marks of a student in each subject from the above School_Stu_Details table:

Output:

GROUP BY clause with MAX function

We can also use the MAX aggregate function with the GROUP BY clause in Structured Query Language.

Syntax of Group BY clause with MAX aggregate function:

Example of MAX aggregate Function with GROUP BY Clause

This example takes the below School_Stu_Details table to understand the concept of GROUP BY clause with SQL MAX aggregate function:

The following INSERT INTO statements insert the record of School students:

The following query simply shows the record of students in the tabular form:

The following query shows the maximum marks of a student in each subject from the above School_Stu_Details table:

Output: