GROUP BY Clause in MySQL | Grouping Records with COUNT(), SUM(), AVG(), MIN() and MAX()

Aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() calculate results for the entire table. However, in many situations, we need summarized information for different categories or groups. This is achieved using the GROUP BY clause.

The GROUP BY clause divides records into groups based on one or more columns. Aggregate functions are then applied separately to each group. This helps generate meaningful reports such as class-wise student count, department-wise salary, city-wise customers, and subject-wise average marks.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of the GROUP BY clause.
• Group records based on one or more columns.
• Use GROUP BY with aggregate functions.
• Generate summarized reports for different categories.
• Write practical SQL queries using GROUP BY.

---

What is GROUP BY?

---

Sample Student Table

Roll_No	Name	Class	Section	Marks	Fee
101	Aarav	XI	A	91	45000
102	Diya	XI	A	84	45000
103	Kabir	XI	B	88	45000
104	Riya	XII	A	95	48000
105	Vivaan	XII	B	82	48000
106	Ananya	XII	B	90	48000

---

Basic Syntax of GROUP BY

SELECT column_name,
       aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

---

Example 1: Count Students Class-wise

Display the total number of students in each class.

SELECT Class,
COUNT(*)
FROM Student
GROUP BY Class;

Output

Class	COUNT(*)
XI	3
XII	3

---

Example 2: Calculate Average Marks Class-wise

SELECT Class,
AVG(Marks)
FROM Student
GROUP BY Class;

Output

Class	AVG(Marks)
XI	87.67
XII	89.00

---

Example 3: Calculate Total Fee Class-wise

SELECT Class,
SUM(Fee)
FROM Student
GROUP BY Class;

This query calculates the total fee collected from each class separately.

---

Example 4: Find Highest Marks in Each Class

SELECT Class,
MAX(Marks)
FROM Student
GROUP BY Class;

---

Example 5: Find Lowest Marks in Each Class

SELECT Class,
MIN(Marks)
FROM Student
GROUP BY Class;

---

Why Do We Use GROUP BY?

Without GROUP BY	With GROUP BY
Returns one result for the entire table.	Returns one result for each group.
Cannot compare categories.	Makes category-wise comparison possible.
Useful for overall summaries.	Useful for class-wise, department-wise, city-wise reports.

---

---

Grouping by Multiple Columns

The GROUP BY clause can group records using more than one column. SQL first creates groups based on the first column and then further divides each group according to the second column.

Syntax

SELECT column1,
       column2,
       aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

---

Example 1: Count Students Class-wise and Section-wise

SELECT Class,
       Section,
       COUNT(*)
FROM Student
GROUP BY Class, Section;

Output

Class	Section	COUNT(*)
XI	A	2
XI	B	1
XII	A	1
XII	B	2

---

Example 2: Average Marks Section-wise

SELECT Section,
       AVG(Marks)
FROM Student
GROUP BY Section;

---

Example 3: Total Fee Section-wise

SELECT Section,
       SUM(Fee)
FROM Student
GROUP BY Section;

---

Example 4: Highest Marks in Each Section

SELECT Section,
       MAX(Marks)
FROM Student
GROUP BY Section;

---

Example 5: Lowest Marks in Each Section

SELECT Section,
       MIN(Marks)
FROM Student
GROUP BY Section;

---

Comparison of Aggregate Functions with GROUP BY

Function	Purpose with GROUP BY	Example
COUNT()	Counts records in each group.	COUNT(*) GROUP BY Class
SUM()	Calculates total for each group.	SUM(Fee) GROUP BY Class
AVG()	Finds average for each group.	AVG(Marks) GROUP BY Section
MIN()	Finds minimum value in each group.	MIN(Marks) GROUP BY Class
MAX()	Finds maximum value in each group.	MAX(Marks) GROUP BY Section

---

Solved Example 1

Display the total number of students in each section.

Solution:

SELECT Section,
COUNT(*)
FROM Student
GROUP BY Section;

---

Solved Example 2

Display the average marks of students in each class.

Solution:

SELECT Class,
AVG(Marks)
FROM Student
GROUP BY Class;

---

Solved Example 3

Display the highest marks obtained in each class.

Solution:

SELECT Class,
MAX(Marks)
FROM Student
GROUP BY Class;

---

Solved Example 4

Display the total fee collected from each section.

Solution:

SELECT Section,
SUM(Fee)
FROM Student
GROUP BY Section;

---

Solved Example 5

Display the lowest marks in each class.

Solution:

SELECT Class,
MIN(Marks)
FROM Student
GROUP BY Class;

---

Real-Life Applications

Organization	Requirement	SQL Example
School	Class-wise student strength	COUNT(*) GROUP BY Class
School	Section-wise average marks	AVG(Marks) GROUP BY Section
Company	Department-wise salary	SUM(Salary) GROUP BY Department
Hospital	Department-wise patient count	COUNT(*) GROUP BY Department
Store	Category-wise sales	SUM(Sales) GROUP BY Category
Bank	Branch-wise customers	COUNT(*) GROUP BY Branch

---

Common Errors

Mistake	Correct Practice
Selecting a non-aggregated column without including it in GROUP BY.	Every non-aggregated column in SELECT should also appear in GROUP BY.
Using GROUP BY without an aggregate function when summarization is expected.	Combine GROUP BY with COUNT(), SUM(), AVG(), MIN(), or MAX() whenever required.
Grouping by the wrong column.	Choose the grouping column based on the required report.
Confusing GROUP BY with ORDER BY.	GROUP BY creates groups, whereas ORDER BY sorts the output.
Writing GROUP BY before WHERE.	The WHERE clause is written before GROUP BY.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to display the total number of students in each class. Write the SQL query using the GROUP BY clause.
2. Write an SQL query to calculate the average marks obtained by students in each section.
3. A school accountant wants to calculate the total fee collected from each class. Write the SQL query.
4. Differentiate between GROUP BY and ORDER BY with suitable examples.
5. Explain why aggregate functions are commonly used with the GROUP BY clause.

---

Multiple Choice Questions

1. The GROUP BY clause is used to:
   • (a) Delete records
   • (b) Group rows having the same values
   • (c) Create tables
   • (d) Modify columns
2. Which SQL clause is commonly used with aggregate functions?
   • (a) ORDER BY
   • (b) GROUP BY
   • (c) ALTER TABLE
   • (d) UPDATE
3. Which query counts the number of students in each class?
   • (a) SELECT COUNT(*) FROM Student;
   • (b) SELECT Class, COUNT(*) FROM Student GROUP BY Class;
   • (c) SELECT GROUP COUNT(*) FROM Student;
   • (d) SELECT COUNT(Class);
4. Which function is generally used with GROUP BY to calculate averages?
   • (a) MAX()
   • (b) MIN()
   • (c) AVG()
   • (d) LENGTH()
5. Which query calculates the total fee class-wise?
   • (a) SELECT SUM(Fee) FROM Student;
   • (b) SELECT Class, SUM(Fee) FROM Student GROUP BY Class;
   • (c) SELECT GROUP SUM(Fee);
   • (d) SELECT Fee GROUP BY Class;
6. When grouping by multiple columns, column names are separated using:
   • (a) Semicolon (;)
   • (b) Comma (,)
   • (c) Colon (:)
   • (d) Slash (/)
7. Which clause is written before GROUP BY in a SELECT statement?
   • (a) WHERE
   • (b) ORDER BY
   • (c) LIMIT
   • (d) HAVING
8. Which clause is used to arrange grouped results in ascending or descending order?
   • (a) GROUP BY
   • (b) ORDER BY
   • (c) WHERE
   • (d) SELECT
9. Which aggregate function returns the highest value in each group?
   • (a) MIN()
   • (b) MAX()
   • (c) COUNT()
   • (d) AVG()
10. Which SQL query displays the average marks section-wise?
    • (a) SELECT AVG(Marks) FROM Student;
    • (b) SELECT Section, AVG(Marks) FROM Student GROUP BY Section;
    • (c) SELECT GROUP AVG(Marks);
    • (d) SELECT Section WHERE AVG(Marks);

---

Quick Revision

Clause / Function	Purpose
GROUP BY	Groups records having the same values.
COUNT()	Counts records in each group.
SUM()	Calculates the total for each group.
AVG()	Calculates the average for each group.
MIN()	Finds the minimum value in each group.
MAX()	Finds the maximum value in each group.

---

Important Points to Remember

• The GROUP BY clause divides records into groups based on one or more columns.
• Aggregate functions calculate results separately for each group.
• Every non-aggregated column in the SELECT statement should also be included in the GROUP BY clause.
• GROUP BY is commonly used with COUNT(), SUM(), AVG(), MIN(), and MAX().
• Multiple columns can be specified in the GROUP BY clause by separating them with commas.
• WHERE filters rows before grouping, whereas GROUP BY creates groups from the filtered rows.
• ORDER BY can be used after GROUP BY to sort the grouped results.
• GROUP BY is widely used to generate reports and summaries in real-world database applications.

---

CBSE Exam Tips

• Understand the difference between GROUP BY and ORDER BY.
• Practice writing queries using GROUP BY with all aggregate functions.
• Remember that non-aggregated columns in the SELECT statement must be included in the GROUP BY clause.
• Read competency-based questions carefully to identify the correct grouping column.
• Revise the order of SQL clauses: SELECT → FROM → WHERE → GROUP BY → ORDER BY.
• Expect practical SQL-writing questions based on school, banking, library, and business scenarios.

---

Summary

The GROUP BY clause is one of the most powerful features of MySQL for generating summarized reports. It groups rows with the same values and allows aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to perform calculations for each group independently. It is widely used in schools, companies, hospitals, banks, and retail businesses to prepare category-wise reports, analyze data efficiently, and support decision-making. A clear understanding of the GROUP BY clause is essential for writing effective SQL queries and achieving excellent scores in the CBSE Class 11 Informatics Practices examination.

---

What's Next?

In the next chapter, you will learn the HAVING Clause in MySQL. You will understand how to filter grouped data after applying the GROUP BY clause and aggregate functions, making your SQL queries even more powerful and efficient.