How to use GROUP BY in SQL?

How to use GROUP BY in SQL?

3 mins read517 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Mar 31, 2023 16:40 IST

The GROUP BY clause in sql is used to group the rows that have identical values by one or more columns. In this article, we will briefly discuss how GROUP BY clause is used in SQL with aggregation function, having clause, order by clause and with the case statement.

2022_08_SQL-GroupBy-1.jpg

In the previous article, we have discussed SELECT statement, ORDER BY, HAVING clause, and aggregation functions in SQL. Now, in this article, we will discuss how to group the identical values of a column present in different rows.

Table of Content

What is GROUP BY in SQL?

GROUP BY in SQL is used to group all the rows that have the same (or identical) value by one or more columns.

The below image shows how the GROUP BY clause works in SQL.

The above table (left table) contains the record of Employees (Employee ID, Name, and Department), and when we apply the GROUP BY clause on the Department column, it returns the result set that includes the unique value in the department column (Sales, Marketing, and Education).

Must Read: SQL DELETE

Must Read: SQL ALTER TABLE

Syntax

 
SELECT column_names
FROM table_name
WHERE conditions
GROUP BY column_names
ORDER BY column_names;
Copy code

Now, we will take some examples to understand how to use the GROUP BY clause in SQL.

Let’s have a Student table that contains the StudentID, Name, Percentage, and corresponding Grade.

Student ID Name Percentage Grade Remark
1001 Ajay 87 A Excellent
1002 Babloo 81 A Excellent
1003 Chhavi 79 B Good
1004 Dheeraj 93 O Excellent
1005 Evina 95 O Excellent
1006 Fredy 80.7 A Excellent
1007 Garima 63 C Poor
1008 Hans 49 F Fail
1009 Ivanka 88 A Excellent
1010 Jai 74 B Good
1011 Kundan 88 A Excellent
1012 Himanshi 37 F Fail
1013 Atul 67 B Good
1014 Jaya 48 F Fail
1015 Aquib 75 B Good

Must Read: Introduction to SQL

Must Read: UPDATE query in SQL

COUNT with GROUP BY

Example: Use the GROUP BY clause to count the number of students by the grade.

Query

 
SELECT Grade, COUNT (StudentID) AS Number of Students
FROM Student
GROUP BY Grade;
Copy code

Output

Grade Number of Students
A 5
B 4
C 1
F 3
O 2

Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views

Must Read: SQL Logical Operator

GROUP BY with HAVING

Example: Find the grade in which the number of students is greater than or equal to 3.

Query

 
SELECT Grade, COUNT (StudentID) AS Number of Students
FROM Student
GROUP BY Grade
HAVING Number of Students >= 3;
Copy code

Output

Grade Number of Students
A 5
B 4
F 3

Must Read: Introduction to Normalization

Must Read: SQL HAVING

GROUP BY with ORDER BY

Example: Use the ORDER BY clause in the above example to arrange the number of students in each grade in descending order.

Query

 
SELECT Grade, COUNT (StudentID) AS Number of Students
FROM Student
GROUP BY Grade
ORDER BY Number of Students DESC;
Copy code

Output

Grade Number of Students
A 5
B 4
F 3
O 2
C 1

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

GROUP BY with CASE

Example: Classify the students in two (Pass or Fail) and count the number of student in both the category.

Query

 
SELECT
CASE
WHEN Percentage > 40 THEN ‘PASS’
ELSE ‘FAIL’
END AS Category,
COUNT (*) AS Number of Students
FROM Student
GROUP BY Grade;
Copy code

Output

Category Number of Students
PASS 14
FAIL 1

Must Read: SQL CASE statement

Must Check: Database and SQL Online Course and Certifications

Conclusion

In this article, we have briefly discussed how to use GROUP BY in SQL with the help of examples.

Hope this article, will help you in your Data Science/Data Analysis journey.

Keep Learning!!

Keep Sharing!!

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments