Grouping Data with GROUP BY

Remember aggregate functions from the last lesson? They gave us one summary for the entire dataset: "The average GPA is 3.6." But what if you want to break that down by category? For example, "What's the average GPA per major?" or "What are total sales per region?" That's where GROUP BY comes in—it's the bridge that connects aggregate functions to categorical analysis. This is one of the most powerful features in SQL!

📚 What You'll Learn

• What GROUP BY does and why it's essential for analysis
• How GROUP BY creates one row per group
• The partnership between GROUP BY and aggregate functions
• Grouping by a single column (e.g., by major, by region)
• Grouping by multiple columns (e.g., by major AND year)
• How GROUP BY changes the meaning of SELECT
• The golden rule: every non-aggregated column MUST appear in GROUP BY
• Using WHERE to filter before grouping
• Using HAVING to filter after grouping
• The complete execution order: WHERE → GROUP BY → HAVING
• Common beginner mistakes and how to avoid them
• Real-world business analytics scenarios
• Hands-on practice exercises

1. What Does GROUP BY Do?

GROUP BY divides your data into groups based on column values, then calculates aggregates for each group separately.

Visual Transformation: From Individual Rows to Groups

SELECT AVG(gpa)
FROM students;

SELECT major, AVG(gpa)
FROM students
GROUP BY major;

See the magic? GROUP BY split students into CS and Biology groups, then calculated the average for each group separately!

2. Basic GROUP BY Syntax

SELECT column_to_group_by, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column_to_group_by;

Example: Average GPA per Major

SELECT 
    major,
    AVG(gpa) AS average_gpa
FROM students
GROUP BY major;

How It Works (Step by Step):

3. Critical Concept: One Row Per Group

This is the most important thing to understand about GROUP BY:

Example: Counting Students per Major

SELECT 
    major,
    COUNT(*) AS student_count
FROM students
GROUP BY major;

Sample Result:

Four unique majors = four rows. Each row shows the major and how many students are in it.

4. The Most Important Rule: SELECT and GROUP BY Must Match

Here's the rule that trips up everyone at first:

Valid Queries (Follow the Rule):

-- ✅ CORRECT: major is in SELECT and GROUP BY
SELECT major, AVG(gpa)
FROM students
GROUP BY major;

-- ✅ CORRECT: Only aggregate in SELECT (no GROUP BY needed)
SELECT AVG(gpa)
FROM students;

-- ✅ CORRECT: Both columns in SELECT appear in GROUP BY
SELECT major, year, AVG(gpa)
FROM students
GROUP BY major, year;

Invalid Query (Breaks the Rule):

-- ❌ WRONG: name is in SELECT but NOT in GROUP BY!
SELECT name, major, AVG(gpa)
FROM students
GROUP BY major;

Why This Fails (Logical Explanation):

5. Grouping by Multiple Columns

You can group by multiple columns to create more specific groups:

-- Average GPA per major AND year
SELECT 
    major,
    year,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major, year;

How Multi-Column Grouping Works:

SQL creates groups based on unique combinations of the columns:

Each combination gets its own row: CS 2023, CS 2024, Biology 2023, Biology 2024.

6. Filtering with WHERE (Before Grouping)

WHERE filters rows before they're grouped:

-- Average GPA per major, but only for students with GPA > 3.0
SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
WHERE gpa > 3.0
GROUP BY major;

Execution Order:

Students with GPA ≤ 3.0 are excluded before grouping happens!

7. Filtering with HAVING (After Grouping)

What if you want to filter based on the aggregate result? That's where HAVING comes in:

-- Show only majors with average GPA above 3.5
SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

WHERE vs. HAVING

Example: Majors with More Than 30 Students

SELECT 
    major,
    COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) > 30;

This filters out majors that have 30 or fewer students. The filtering happens after counting!

8. The Complete SQL Execution Order

Understanding the order SQL processes your query is crucial:

Example Using All Clauses:

SELECT 
    major,
    AVG(gpa) AS avg_gpa,
    COUNT(*) AS student_count
FROM students
WHERE year >= 2023                    -- 1. Filter: only recent students
GROUP BY major                        -- 2. Group by major
HAVING AVG(gpa) > 3.4                 -- 3. Filter: only high-performing majors
ORDER BY avg_gpa DESC                 -- 4. Sort: highest GPA first
LIMIT 5;                              -- 5. Show: top 5 majors only

9. Common Beginner Mistakes

Mistake #1: Missing Column in GROUP BY

-- ❌ WRONG: name is in SELECT but not in GROUP BY
SELECT name, major, AVG(gpa)
FROM students
GROUP BY major;

-- ✅ CORRECT: Remove name, or add it to GROUP BY
SELECT major, AVG(gpa)
FROM students
GROUP BY major;

Mistake #2: Using WHERE Instead of HAVING

-- ❌ WRONG: Can't use aggregates in WHERE
SELECT major, AVG(gpa)
FROM students
WHERE AVG(gpa) > 3.5
GROUP BY major;

-- ✅ CORRECT: Use HAVING for aggregates
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

Mistake #3: Forgetting GROUP BY Entirely

-- ❌ WRONG: major + aggregate without GROUP BY
SELECT major, AVG(gpa)
FROM students;

-- ✅ CORRECT: Add GROUP BY
SELECT major, AVG(gpa)
FROM students
GROUP BY major;

Mistake #4: Wrong Column Order Expectation

-- Both of these work and give the same result:
SELECT major, AVG(gpa) FROM students GROUP BY major;
SELECT AVG(gpa), major FROM students GROUP BY major;

-- But the column display order is different!
-- First query: major column, then avg_gpa column
-- Second query: avg_gpa column, then major column

Mistake #5: Using HAVING Without GROUP BY

-- ❌ WRONG: HAVING without GROUP BY
SELECT AVG(gpa)
FROM students
HAVING AVG(gpa) > 3.5;

-- ✅ CORRECT: Use WHERE for non-grouped queries
SELECT AVG(gpa)
FROM students
WHERE gpa > 3.5;

10. 🎯 Hands-On Practice Exercises

SELECT 
    major,
    COUNT(*) AS student_count
FROM students
GROUP BY major;

SELECT 
    major,
    AVG(gpa) AS average_gpa
FROM students
GROUP BY major;

SELECT 
    major,
    COUNT(*) AS student_count,
    AVG(gpa) AS avg_gpa,
    MIN(gpa) AS lowest_gpa,
    MAX(gpa) AS highest_gpa
FROM students
GROUP BY major;

SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
WHERE gpa >= 3.0
GROUP BY major;

SELECT 
    major,
    COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) > 40;

SELECT 
    major,
    year,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major, year;

SELECT 
    major,
    AVG(gpa) AS avg_gpa,
    COUNT(*) AS student_count
FROM students
WHERE year >= 2023
GROUP BY major
HAVING AVG(gpa) > 3.5
ORDER BY avg_gpa DESC
LIMIT 3;

📝 Key Takeaways

• GROUP BY divides data into groups and calculates aggregates per group
• Produces one row per unique group
• Golden Rule: Non-aggregated columns in SELECT must be in GROUP BY
• Can group by single or multiple columns
• WHERE filters rows before grouping
• HAVING filters groups after grouping
• Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• GROUP BY column order doesn't matter (but SELECT order does)
• Can combine multiple aggregates in one query
• Essential for business analytics, dashboards, and reports