SQL

GROUP BY in SQL

📊 The GROUP BY clause in SQL is used to group rows that have the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to generate summarized reports and analyze data.

📖 What is GROUP BY?

The GROUP BY clause combines rows with identical values into groups. Aggregate functions are then applied to each group instead of the entire table.

Information

Without GROUP BY, aggregate functions calculate a single result for the entire dataset. With GROUP BY, a separate result is calculated for each group.

📝 Basic Syntax

GROUP BY Syntax

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

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	City	Marks	FeesPaid
101	Alice	Computer Science	Chennai	92	50000
102	Bob	Mathematics	Coimbatore	85	45000
103	Charlie	Physics	Madurai	78	40000
104	David	Computer Science	Chennai	95	50000
105	Eva	Mathematics	Salem	88	47000

1️⃣ GROUP BY with COUNT()

Count the number of students in each department.

Count Students by Department

SELECT Department,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department;

Result:

Department	TotalStudents
Computer Science	2
Mathematics	2
Physics	1

2️⃣ GROUP BY with SUM()

Calculate the total fees collected from each department.

Total Fees by Department

SELECT Department,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department;

3️⃣ GROUP BY with AVG()

Calculate the average marks for each department.

Average Marks by Department

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department;

4️⃣ GROUP BY with MIN() and MAX()

Find the lowest and highest marks for each department.

Minimum and Maximum Marks

SELECT Department,
       MIN(Marks) AS LowestMarks,
       MAX(Marks) AS HighestMarks
FROM Students
GROUP BY Department;

📍 GROUP BY Multiple Columns

You can group records using more than one column.

Group by Department and City

SELECT Department,
       City,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department,
         City;

This query creates a separate group for every unique combination of Department and City.

🔍 GROUP BY with WHERE

The WHERE clause filters rows before grouping.

Filtered Groups

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
WHERE Marks >= 80
GROUP BY Department;

📈 GROUP BY with HAVING

The HAVING clause filters groups after aggregate calculations.

Departments with More Than One Student

SELECT Department,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department
HAVING COUNT(*) > 1;

Tip

Remember the difference:
• WHERE filters individual rows.
• HAVING filters grouped results.

📊 GROUP BY with ORDER BY

Use ORDER BY to sort grouped results.

Sort Grouped Results

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
ORDER BY AverageMarks DESC;

🔗 GROUP BY with JOIN

The GROUP BY clause is commonly used with joins to summarize data across multiple tables.

Count Courses per Student

SELECT s.Name,
       COUNT(c.CourseID) AS TotalCourses
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name;

⚠️ GROUP BY Rules

Rule	Description
Grouped Columns	Every non-aggregated column in the SELECT list should appear in the GROUP BY clause.
Aggregate Functions	Aggregate functions operate on each group individually.
WHERE	Filters rows before grouping.
HAVING	Filters groups after aggregation.

📊 Common Aggregate Functions with GROUP BY

Function	Purpose
COUNT()	Counts records.
SUM()	Calculates totals.
AVG()	Calculates averages.
MIN()	Returns the smallest value.
MAX()	Returns the largest value.

💼 Real-World Example

A university administrator wants to generate a report showing the number of students, average marks, and total fees collected for each department.

Department Summary Report

SELECT Department,
       COUNT(*) AS StudentCount,
       AVG(Marks) AS AverageMarks,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department
ORDER BY StudentCount DESC;

This query creates a summary report that combines multiple aggregate functions for every department.

⚠️ Best Practices

Best Practice

Use GROUP BY when summarizing data with aggregate functions. Include every non-aggregated selected column in the GROUP BY clause, use WHERE to filter rows before grouping, use HAVING to filter groups after aggregation, and apply ORDER BY to present grouped results clearly.

🚀 Key Points to Remember

📌 GROUP BY groups rows with identical values.
📌 It is commonly used with aggregate functions.
📌 WHERE filters rows before grouping.
📌 HAVING filters groups after aggregation.
📌 Multiple columns can be used in the GROUP BY clause.
📌 ORDER BY can sort grouped results.

>>"The GROUP BY clause transforms individual rows into meaningful summaries, making data analysis simple and powerful."

Summary

✅ The GROUP BY clause is one of SQL's most powerful features for summarizing and analyzing data. When combined with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX(), it enables efficient reporting, business intelligence, and data-driven decision-making.