The GROUP BY Clause

Definition: The GROUP BY clause is used to arrange identical data into groups. It is most often used with aggregate functions (like COUNT, SUM, or AVG) to provide a summary for each group rather than for the entire table.

Why: Grouping is a major milestone in beginner SQL tutorials because it is the foundation of summary reporting. Instead of just knowing you have 100 students, GROUP BY allows you to see how those students are distributed across different categories, such as city, age, or course enrollment.

Syntax

The GROUP BY clause follows the WHERE clause (if present) and precedes the ORDER BY clause. You list the column(s) that contain the repeating values you want to group together.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

Example: Student Count by City

To find out how many students live in each city, you group the records by the city column and count the rows in each resulting group:

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

Explanation

• Grouping Mechanism: The database engine looks at the city column. It puts all "Bengaluru" records in one bucket, all "Mysuru" records in another, and so on.
• Aggregation: The COUNT(*) function is then applied to each bucket individually.
• AS total_students: This is an "alias" that gives the resulting count a clear, readable header in your report.

Key Notes

• The "Select" Rule: When using GROUP BY, every column in your SELECT list must either be part of the GROUP BY clause or be inside an aggregate function. You cannot select a specific student's name if you are grouping by city.
• Filtering Groups: If you want to filter the results after they have been grouped (for example, to show only cities with more than 5 students), you must use the HAVING clause, as WHERE only works on individual rows.
• Sorting: You can combine this with ORDER BY to sort your groups. For example, ORDER BY total_students DESC would show the cities with the most students first.