Python SQL GROUP BY: Aggregation Buckets & Categorical Metrics
The `GROUP BY` clause groups rows that share identical values in specified columns into summary rows (buckets). You can then calculate aggregate metrics (COUNT, SUM, AVG, MIN, MAX) independently for each individual group.
"`GROUP BY department` is like sorting a giant stack of mail into separate departmental sorting trays (Sales, Engineering, HR) and counting how many letters landed in each tray."
Deep Dive: How It Works
The ANSI Single-Value Rule: In standard SQL, EVERY column in the SELECT list that is NOT inside an aggregate function MUST be listed in the `GROUP BY` clause.
Multi-Column Grouping: `GROUP BY country, city` creates a separate aggregation bucket for each unique country-city pair.
Execution Timing: `GROUP BY` executes AFTER `WHERE` filters rows, but BEFORE `HAVING` and `SELECT`.
Syntax Blueprint
SELECT department, COUNT(*) AS head_count, AVG(salary) AS avg_sal FROM employees GROUP BY department ORDER BY avg_sal DESC;
Rows are partitioned into departmental buckets before aggregates are computed per group.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Selecting columns not present in GROUP BY: `SELECT department, name, AVG(salary) FROM employees GROUP BY department;`.Why it happens: There are multiple names per department; the engine cannot know which single name to display.
How to fix: Remove `name` from SELECT or add it to GROUP BY.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Group Sales by Category
- Select `category`, `COUNT(*) AS count`, `SUM(price) AS total` from `inventory` grouped by `category`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.