Group By Aggregation: A First Look
Think of a pile of laundry. You have socks, shirts, towels — all mixed together. If someone asks "how many socks are there?", you first separate the socks from everything else, then count them. That act of separating first, then summarising — that is the core of Group By Aggregation.
In data, we often have rows of information about many different things mixed together. Group By Aggregation is the operation that says: "Take all these rows, sort them into meaningful groups based on some common property, and then for each group, compute a single summary value."
The Two Steps, Always
Every Group By Aggregation has exactly two parts, and they happen in order:
- Grouping — You pick a column (or columns) whose values will define the groups. All rows that share the same value in that column become one group.
- Aggregating — You pick a summary operation to apply to each group. Common operations include counting how many rows are in the group, finding the total or average of a numeric column within the group, or picking the smallest or largest value.
The result is a new table: one row per group, with the group's label and the computed summary.
Why It Matters
Without Group By, you can only summarise the entire dataset at once — one total, one average for everything. That hides almost all the interesting variation. Group By lets you ask questions like:
- For each state, what is the total number of schools?
- For each year, what was the average rainfall?
- For each product category, how many items were sold?
Each of these questions picks a grouping column (state, year, category) and an aggregation (total, average, count). The answer reveals patterns that a single overall number would bury.
A Concrete Example (Without Numbers)
Imagine a table of exam scores for students from different cities. The table has columns: Student Name, City, Score.
If you want to know "how many students appeared from each city", you:
- Group by the
City column — all rows with the same city name become one group.
- Apply the count aggregation — count how many rows are in each group.
The result is a table with two columns: City and Number of Students.
If instead you want "the highest score in each city", you:
- Group by
City again.
- Apply the maximum aggregation to the
Score column.
The result is a table with City and Highest Score.
The grouping column and the aggregation column are often different. The grouping column defines who is in each group; the aggregation column defines what you summarise about that group. You can also aggregate the same column you grouped by — for example, counting how many rows are in each group — but that is a special case, not the general rule.
Common Aggregation Operations
These are the standard summaries you can compute for each group:
- Count — How many rows are in the group.
- Sum — Total of a numeric column across all rows in the group.
- Average — Mean of a numeric column across the group.
- Minimum — Smallest value in a numeric column within the group.
- Maximum — Largest value in a numeric column within the group.
Each of these gives a different lens on the same grouped data.
What the NCERT Textbook Says …