Grouping Data with GROUP BY

When you're working with large datasets, sometimes you need to organize and summarize your data by categories. That's exactly what GROUP BY helps you do. It's commonly used with aggregate functions like COUNT() , SUM() , AVG() , MIN() , and MAX() .

📘 What is GROUP BY ?

The GROUP BY clause groups rows that have the same values in specified columns into summary rows — like total marks per course , average age per city , etc.

It’s like saying: "Group all students by course, then show average marks in each course."

🧾 Basic Syntax

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

🎓 Let's Use Our Sample Table: students

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	91.75
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	Advanced SQL	76.00

🧮 Example 1: Average Marks Per Course

SELECT course, AVG (marks) AS average_marks 
FROM students GROUP BY course;  

Output:

course	average_marks
Advanced SQL	87.19
Python	92.50

🔢 Example 2: Number of Students Per Course

SELECT course, COUNT ( * ) AS total_students 
FROM students GROUP BY course;  

Output:

course	total_students
Advanced SQL	4
Python	1

📉 Example 3: Minimum and Maximum Marks Per Course

SELECT 
	course, 
    MIN (marks) AS min_marks, 
    MAX (marks) AS max_marks 
FROM 
 	students 
GROUP BY 
  	course;  

Output:

course	min_marks	max_marks
Advanced SQL	76.00	91.75
Python	92.50	92.50

🎯 Using GROUP BY with WHERE

You can filter records before grouping them using the WHERE clause.

SELECT course, AVG (marks) 
FROM students WHERE age > 21 GROUP BY course;  

🧮 Using GROUP BY with Multiple Columns

You can also group by more than one column.

SELECT course, age, COUNT ( * ) AS students_in_group 
FROM students GROUP BY course, age;  

This groups students by both course and age .

⚠️ Common Mistakes

• Every selected column not used with an aggregate function must appear in the GROUP BY clause.

❌ Invalid:

SELECT course, name, AVG (marks) 
FROM students GROUP BY course;  

✅ Correct:

SELECT course, AVG (marks) 
FROM students GROUP BY course;  

🧠 Real-World Examples

Scenario	Query
Total orders per customer	GROUP BY customer_id
Sales per region	GROUP BY region
Views per blog category	GROUP BY category

📝 Summary

• GROUP BY organizes rows into groups.
• Commonly used with aggregate functions .
• Can group by one or more columns.
• Use WHERE to filter before grouping.