Pathubs SQL Curriculum • Module 12

SQL GROUP BY

Master the foundational concept of SQL analytics: learn how GROUP BY segments rows into distinct groups, calculates aggregate functions separately for each bucket, handles multi-column grouping, and combines with WHERE.

⏱️ Estimated Time:50 Minutes

🎯 Level:Beginner to Intermediate

📊 Track:Data Analytics & SQL Mastery

✨ Mode:Interactive Group Visualizer & Aggregate Switcher

Introduction

Until now, aggregate functions (like COUNT, SUM, and AVG) have produced a single summary result for the entire table.

Without GROUP BY:

One grand total for the whole dataset (e.g. ₹1,700 total sales).

With GROUP BY:

One summary result calculated separately for each category (e.g. Electronics: ₹1,200, Clothing: ₹500).

Consider a realistic sales table:

Product	Category	Amount
A	Electronics	₹500
B	Clothing	₹300
C	Electronics	₹700
D	Clothing	₹200

What Does GROUP BY Do?

GROUP BY collects rows that share the same value(s) in specified columns into summary buckets:

SELECT category
FROM sales
GROUP BY category;

While grouping alone produces unique category rows, its true analytical power is unlocked when paired with aggregate functions.

Diagram 1: Core GROUP BY Concept

Raw Rows:
Electronics
Electronics
Clothing
Clothing
Furniture

➔ GROUP BY category ➔

Separate Group Buckets:
• [Electronics: 2 rows]
• [Clothing: 2 rows]
• [Furniture: 1 row]

GROUP BY + COUNT

The most intuitive example counts the number of rows belonging to each group:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

Step-by-Step Breakdown:

1. Scan all rows in products.
2. Group rows by category (Electronics group, Clothing group, Furniture group).
3. Compute COUNT(*) independently inside each group.

Diagram 2: GROUP BY + COUNT Transformation

Electronics ➔ 3 rows ➔ COUNT = 3

Clothing ➔ 2 rows ➔ COUNT = 2

Furniture ➔ 4 rows ➔ COUNT = 4

GROUP BY + SUM

Calculate total sales volume per category:

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category;

Instead of adding all sales numbers together, SQL computes the sum for Electronics (500 + 700 = 1200) and Clothing (300 + 200 = 500) separately.

GROUP BY + AVG

Calculate departmental salary benchmarks:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Each department receives its own isolated arithmetic mean.

GROUP BY + MIN / MAX

You can calculate multiple aggregate functions for each group simultaneously:

SELECT category,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products
GROUP BY category;

The Most Important Mental Model

Internalize this transformation pipeline as the cornerstone of SQL data analytics:

Original Table (Many Rows)
↓ GROUP BY category
┌────────────────────────┐
│ Electronics Bucket (2) │
│ Clothing Bucket (2)    │
│ Furniture Bucket (1)   │
└────────────────────────┘
↓ Aggregate Each Bucket (SUM, COUNT, etc.)
One Result Row Per Group

GROUP BY vs DISTINCT

SELECT DISTINCT category

Returns only the unique category names by deduplicating rows. Does NOT perform aggregate calculations.

SELECT category, COUNT(*) GROUP BY category

Creates group buckets specifically designed so that aggregate functions (COUNT, SUM, AVG) can compute per group.

GROUP BY Multiple Columns

Grouping by multiple columns forms groups based on each unique combination of column values:

SELECT city, department, COUNT(*) AS employees
FROM employees
GROUP BY city, department;

Here, Mumbai + Sales, Mumbai + HR, and Delhi + Sales are three separate, distinct groups.

Diagram 3: Multi-Column Grouping Combinations

Mumbai | Sales
Mumbai | Sales
Mumbai | HR
Delhi | Sales

➔ GROUP BY city, department ➔

Combination Groups:
• Mumbai + Sales (2 employees)
• Mumbai + HR (1 employee)
• Delhi + Sales (1 employee)

GROUP BY With WHERE

When a query includes both WHERE and GROUP BY:

SELECT department, COUNT(*) AS employees
FROM employees
WHERE city = 'Mumbai'
GROUP BY department;

🧠

Mental Model: Table ➔ WHERE (filters rows) ➔ GROUP BY (creates groups from remaining rows) ➔ Aggregate (calculates each group) ➔ Result.

Understanding Query Structure

Look at this complete analytical query:

SELECT department,
COUNT(*) AS employees,
AVG(salary) AS average_salary
FROM employees
WHERE city = 'Mumbai'
GROUP BY department;

SELECT: Defines which group keys and aggregate results appear in the output.
FROM: Identifies the source data table.
WHERE: Filters individual records before grouping occurs.
GROUP BY: Clusters matching rows into distinct groups.
COUNT / AVG: Computes statistical calculations inside each bucket.

⚡ Live Interactive GROUP BY Lab

Group By Column(s):

Aggregate Function:

🌲 Group Visualizer (Clustered Rows)3 groups (8 rows)

📁 Sales

COUNT: 4 employees

├──Aarav Mehta(Mumbai, ₹65,000)

├──Rohan Verma(Delhi, ₹58,000)

├──Ananya Iyer(Mumbai, ₹72,000)

└──Rahul Roy(Delhi, ₹61,000)

📁 HR

COUNT: 2 employees

├──Priya Sharma(Mumbai, ₹52,000)

└──Neha Gupta(Delhi, ₹48,000)

📁 Engineering

COUNT: 2 employees

├──Vikram Singh(Bengaluru, ₹90,000)

└──Kavita Nair(Bengaluru, ₹85,000)

✍️ Generated Query & Output● One Row Per Group

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

department	COUNT Result
Sales	4 employees
HR	2 employees
Engineering	2 employees

Common GROUP BY Mistakes

1. Selecting Non-Grouped, Non-Aggregated Columns

SELECT department, employee, COUNT(*) ... GROUP BY department; is invalid in standard SQL because the database does not know which employee row to pick for a collapsed department group.

2. Confusing WHERE with Post-Group Filtering

WHERE filters raw rows before grouping. You cannot write WHERE COUNT(*) > 2 because groups do not exist yet when WHERE executes (filtering groups requires HAVING).

3. Confusing GROUP BY with DISTINCT

DISTINCT only eliminates duplicate rows in the final output; it cannot compute per-group summaries like SUM, AVG, or COUNT.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT department, COUNT(*) FROM employees GROUP BY department;

How many result rows will this query produce for the 8 employees above across Sales, HR, and Engineering?

Practical Step-by-Step Exercises

Task Goal	Target Table	Required SQL Solution	Grouping Pattern
1. Count Employees per Dept	`employees`	`SELECT department, COUNT(*) FROM employees GROUP BY department;`	Single-column COUNT
2. Total Sales per Category	`sales`	`SELECT category, SUM(amount) FROM sales GROUP BY category;`	Single-column SUM
3. Average Salary per Dept	`employees`	`SELECT department, AVG(salary) FROM employees GROUP BY department;`	Single-column AVG
4. Min/Max Price per Category	`products`	`SELECT category, MIN(price), MAX(price) FROM products GROUP BY category;`	Multi-aggregate group
5. City + Department Grouping	`employees`	`SELECT city, department, COUNT(*) FROM employees GROUP BY city, department;`	Multi-column grouping
6. Filtered Department Count	`employees`	`SELECT department, COUNT(*) FROM employees WHERE city = 'Mumbai' GROUP BY department;`	WHERE + GROUP BY

GROUP BY Best Practices

Match SELECT Columns with GROUP BY: Ensure every non-aggregated column in your SELECT list is explicitly included in GROUP BY.
Meaningful Aliases: Always assign descriptive aliases (e.g. COUNT(*) AS total_employees) for readable report headers.
Filter Early with WHERE: Reduce row count using WHERE before grouping to improve query performance.
Order Your Results: Combine with ORDER BY (e.g. ORDER BY total_sales DESC) to rank your grouped summaries.

What You Should Know Now

✓GROUP BY: Transforms rows into grouped summary buckets
✓Per-Group Aggregates: COUNT, SUM, AVG, MIN, MAX calculate per bucket
✓Multi-Column: Groups by unique combinations of columns
✓WHERE + GROUP BY: Rows are filtered first before grouping
✓GROUP BY vs DISTINCT: GROUP BY enables statistical aggregations
✓One Row Output: Each distinct group produces exactly 1 result row

🎯 Knowledge Check Quiz: SQL GROUP BY

Test your understanding of group clustering, per-group aggregates, multi-column combinations, and the WHERE + GROUP BY mental model.

1. What is the primary purpose of the SQL GROUP BY clause?

2. How many rows does `SELECT department, COUNT(*) FROM employees GROUP BY department;` return?

3. What is the fundamental difference between `SELECT DISTINCT category FROM products;` and `SELECT category, COUNT(*) FROM products GROUP BY category;`?

4. When grouping by multiple columns (e.g. `GROUP BY city, department`), how are groups determined?

5. In a query with both WHERE and GROUP BY, what is the correct execution order in the SQL mental model?

6. Which of the following queries violates the SQL standard Single-Value Rule regarding non-aggregated columns in GROUP BY?

7. Can you compute multiple aggregate functions (e.g. MIN, MAX, AVG, COUNT) in a single GROUP BY query?

8. If you want to filter out groups after aggregation (e.g. "show only departments where employee count > 2"), which clause should be used in later modules?