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:

1. 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.
2. 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.

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 …

Relational Algebra Operations: A First Look

Think of a relational database as a collection of neat, rectangular tables. Each table has rows (records) and columns (attributes). Now, suppose you want to ask questions of this data — "Which customers live in Delhi?" or "Show me all orders placed last month." Relational algebra is the set of basic operations you use to answer such questions. It is the language of queries at the most fundamental level.

You do not need to write code or do math. You only need to understand what each operation does to a table — like a set of tools in a toolbox.

The Core Operations

There are eight classic operations. They fall into two groups: those that work on one table at a time, and those that combine two tables.

Operations on a Single Table

Select (also called Restrict) — This operation picks certain rows from a table based on a condition. For example, from a table of students, you might select only those rows where the city is "Mumbai". The result is a smaller table with the same columns but fewer rows.

Project — This operation picks certain columns from a table. For example, from a student table with columns Roll No, Name, City, and Marks, you might project only Name and City. The result is a table with fewer columns. Duplicate rows are automatically removed.

Rename — This operation simply gives a new name to the resulting table or to its columns. It is useful when you need to refer to the same table more than once in a query, or when you want clearer column headings.

Operations That Combine Two Tables

Union — This combines two tables that have the same structure (same number of columns and compatible data types). The result contains all rows that appear in either table, with duplicates removed. Think of it as "add the rows of one table to the rows of another, but keep only unique ones."

Set Difference — This gives you rows that are in the first table but not in the second. For example, "Which students are enrolled in Course A but not in Course B?"

Intersection — This gives you rows that appear in both tables. For example, "Which customers have bought both a laptop and a printer?"

Cartesian Product — This pairs every row of the first table with every row of the second table. If the first table has 10 rows and the second has 5, the result has 50 rows. This operation is rarely used alone — it is the foundation for the most powerful operation of all.

Join — This is the heart of relational algebra. A join combines rows from two tables based on a related column between them. For example, you have a Customers table and an Orders table. The join operation matches each order to the customer who placed it, using the Customer ID column that appears in both tables. The result is a single table with all the customer details alongside their orders.

Why Relational Algebra Matters …