GROUP BY Clause
Summarizing Data in SQL

Introduction

Imagine your school wants to know how many students are in each class, or the average marks scored by subject. The GROUP BY clause is how you group similar data points together and summarize them — turning rows into meaningful insights.

Syntax of GROUP BY

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

Sample Table – student_marks

Let’s consider the following table:

CREATE TABLE student_marks (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_marks VALUES
(1, 'Aarav Sharma', '10A', 'Maths', 85),
(2, 'Diya Iyer', '9B', 'Maths', 90),
(3, 'Rohit Menon', '10A', 'Maths', 78),
(4, 'Sneha Patil', '8C', 'Maths', 88),
(5, 'Mehul Agarwal', '9B', 'Maths', 92),
(1, 'Aarav Sharma', '10A', 'Science', 91),
(2, 'Diya Iyer', '9B', 'Science', 87),
(3, 'Rohit Menon', '10A', 'Science', 85),
(4, 'Sneha Patil', '8C', 'Science', 89),
(5, 'Mehul Agarwal', '9B', 'Science', 93);

1. Count Students per Class

Let’s count how many marks entries exist per class:

SELECT class, COUNT(*) AS total_entries
FROM student_marks
GROUP BY class;

class | total_entries
------+---------------
10A   | 4
9B    | 4
8C    | 2

2. Average Marks by Subject

To calculate the average marks for each subject:

SELECT subject, AVG(marks) AS avg_marks
FROM student_marks
GROUP BY subject;

subject | avg_marks
--------+-----------
Maths   | 86.6
Science | 89.0

3. Highest Marks per Class

Let’s find the top marks in each class (across all subjects):

SELECT class, MAX(marks) AS top_score
FROM student_marks
GROUP BY class;

4. Grouping by Multiple Columns

Now, let’s see average marks per subject per class:

SELECT class, subject, AVG(marks) AS avg_marks
FROM student_marks
GROUP BY class, subject;

class | subject | avg_marks
------+---------+-----------
10A   | Maths   | 81.5
10A   | Science | 88.0
9B    | Maths   | 91.0
9B    | Science | 90.0
8C    | Maths   | 88.0
8C    | Science | 89.0

5. Using GROUP BY with WHERE

What if we only want to group Maths marks?

SELECT class, AVG(marks) AS avg_maths
FROM student_marks
WHERE subject = 'Maths'
GROUP BY class;

6. HAVING vs WHERE

WHERE filters before grouping. HAVING filters after. For example:

Show only subjects with average marks greater than 88:

SELECT subject, AVG(marks) AS avg_marks
FROM student_marks
GROUP BY subject
HAVING AVG(marks) > 88;

subject | avg_marks
--------+-----------
Science | 89.0

Best Practices

• Always include grouped columns in SELECT if not using an aggregate function.
• Use HAVING when filtering aggregated results.
• Group logically: by class, subject, or both — depending on your analysis goal.

Summary

GROUP BY is your tool for summaries. It transforms lists into reports, raw data into insights. Whether you're calculating subject-wise averages or class-level toppers, this clause does the heavy lifting with just a few lines.

What’s Next?

Now that you can group and summarize, it’s time to narrow down the output — using the LIMIT clause to fetch only top records, like the top 3 scorers in a subject.