SQL GROUP BY Clause — Summarizing Data

The office needs the number of students in each class rather than a separate report row for each student.

GROUP BY combines rows with the same grouping values into groups so aggregate functions can compute one summary for each group.

The grouping columns define what one result row represents. COUNT(*) counts input rows, COUNT(column) ignores NULL in that expression, and COUNT(DISTINCT roll_no) counts distinct non-null students. In a marks table with several subjects per student, counting rows counts mark records rather than people.

Related concepts: Filter Groups with HAVING and Remove Duplicate Results and SQL Functions.

SQL · portable concepts; preserved examples identify their engine

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

GROUP BY column1
The GROUP BY clause is used to group rows in a result set based on the values in one or more columns. In this syntax, column1 is the grouping column. All rows with the same value in column1 are grouped together.

Once the rows are grouped, aggregate functions like SUM(), AVG(), COUNT(), MIN(), or MAX() can be applied to each group to produce summary results for each group.

Key Points:

• Every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause.
• The GROUP BY clause comes after the WHERE clause and before the ORDER BY clause (if used).
• You can group by one or more columns, depending on the level of detail needed.

SQL · portable concepts; preserved examples identify their engine

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);

SQL · portable concepts; preserved examples identify their engine

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

Use case: A school administrator wants to find out how many student mark records exist for each class to analyze data entry volume by class.

SQL query description: SQL query to select the class column and count the number of entries for each class from the student_marks table using the GROUP BY clause.

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

Result description: Result contains each class along with the total number of student mark entries recorded for that class.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

To calculate the average marks for each subject:

Use case: A school administrator wants to calculate the average marks obtained by students in each subject to analyze overall subject performance.

SQL query description: SQL query to select the subject and the average of marks (renamed as avg_marks) from the student_marks table, grouping the results by subject.

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

Result description: Result contains each subject along with the corresponding average marks scored by all students in that subject.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

Use case: A school administrator wants to find the highest marks scored in each class to identify top performers across all classes.

SQL query description: SQL query to select the class and the maximum value of marks (aliased as top_score) from the student_marks table, grouped by class.

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

Result description: Result contains one row per class, showing the class name and the highest marks obtained in that class.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

Use case: A school administrator wants to calculate the average marks for each subject across all classes to analyze academic performance trends.

SQL query description: SQL query to select the class, subject, and the average of marks (aliased as avg_marks) from the student_marks table, grouped by class and subject.

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

Result description: Result contains the average marks for each subject in every class, showing one row per class-subject combination.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

What if we only want to group Maths marks?

Use case: A school administrator wants to calculate the average marks in Maths for each class to analyze overall class performance in the subject.

SQL query description: SQL query to select the class and the average of marks (renamed as avg_maths) from the student_marks table where the subject is 'Maths', grouping the results by class.

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

Result description: Result contains the average Maths marks for each class, with one row per class.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

Use case: A school administrator wants to identify subjects where the average student marks are higher than 88, in order to recognize high-performing subjects or teachers.

SQL query description: SQL query to select the subject and the average of marks (aliased as avg_marks) from the student_marks table. It groups the records by subject and includes only those groups where the average marks are greater than 88 using the HAVING clause.

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

Result description: Result contains the names of subjects and their average marks, limited to those subjects where the average is greater than 88.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• 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.

Do not select an arbitrary ungrouped student name beside a class summary. Some engines accept limited functional-dependency cases, but portable examples group every non-aggregated selected field. GROUP BY does not guarantee a sorted result.

Count students in each class and specify an order for the summary.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT class, COUNT(*) AS students FROM practice_students GROUP BY class ORDER BY class;

The three student rows form two class groups. In the preserved multi-subject examples, COUNT(*) instead counts marks entries because that table has one row per subject result.

GROUP BY combines rows with the same grouping values into groups so aggregate functions can compute one summary for each group.

The three student rows form two class groups. In the preserved multi-subject examples, COUNT(*) instead counts marks entries because that table has one row per subject result.

Continue your understanding with Filter Groups with HAVING and Remove Duplicate Results and SQL Functions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.