Module 16 — GROUP BY & HAVING

1. GROUP BY — Aggregate Per Group

What it is: GROUP BY groups rows that share the same value in one or more columns, then applies aggregate functions (COUNT, SUM, AVG, etc.) to each group. Instead of one result for the whole table, you get one result per group.

Why we use it: Raw data is often too detailed — "how many products per category?" is more useful than listing every product. GROUP BY turns row-level data into summary statistics.

When we use it: In reports, dashboards, analytics — any time you need "per category", "per user", "per month", "per status" summaries.

-- Count products per category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
 
-- Average price per category
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

2. GROUP BY Multiple Columns

What it is: You can group by multiple columns to create more granular groups. For example, grouping by both category and brand gives you a group for each unique combination.

Why we use it: Single-column grouping is often too coarse — "products per category" doesn't tell you about brands within categories. Multi-column grouping provides more detailed breakdowns.

When we use it: When you need hierarchical summaries, multi-dimensional analysis, or when a single grouping column isn't sufficient.

-- Count products per category AND brand
SELECT category, brand, COUNT(*) AS count
FROM products
GROUP BY category, brand;

3. HAVING — Filter Groups After Aggregation

What it is: HAVING filters groups after aggregation, similar to how WHERE filters rows before grouping. You can't use WHERE to filter on aggregate values (like COUNT(*) > 5) — that's what HAVING is for.

Why we use it: Some conditions only make sense after grouping — "categories with more than 5 products", "users with average order value over $100", "months with revenue above target". HAVING filters these aggregated results.

When we use it: Every time you need to filter on aggregate values — top N groups, groups above/below thresholds, or groups meeting specific criteria.

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

-- Categories with more than 5 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
 
-- Categories with average price over 10000
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category

4. WHERE vs HAVING

What it is: WHERE and HAVING both filter data, but at different stages of query execution. WHERE filters individual rows before grouping, HAVING filters groups after aggregation. Understanding this difference is critical for writing correct queries.

Why we use it: Using WHERE when you need HAVING (or vice versa) is a common bug. The query either fails (using aggregate in WHERE) or returns wrong results (filtering rows when you meant to filter groups).

When we use it: Every time you write a GROUP BY query — you need to decide which filters go in WHERE (row-level) and which go in HAVING (group-level).

-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER aggregation
SELECT category, AVG(price) AS avg_price
FROM products
WHERE price > 1000          -- Filter rows first
GROUP BY category
HAVING AVG(price) > 5000;   -- Then filter groups

5. ORDER BY with GROUP BY

What it is: You can combine ORDER BY with GROUP BY to sort the grouped results. Without ORDER BY, the order of groups is unpredictable.

Why we use it: Grouped results are often more useful when sorted — "top categories by product count", "users sorted by order total", "months sorted by revenue".

When we use it: Every time you want grouped results in a specific order — ranked lists, sorted reports, or paginated summaries.

-- Categories by product count (highest first)
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

Common Mistakes to Avoid

1. Selecting non-grouped columns — Every non-aggregate column must be in GROUP BY
2. Using WHERE instead of HAVING for aggregates — WHERE AVG(price) > 100 is invalid; use HAVING
3. Forgetting ORDER BY — Group results are unordered by default

Practice Exercise

Using your courses table:

1. Count courses per instructor
2. Find the average price per instructor
3. Find instructors who have more than 2 courses (use HAVING)
4. Find categories where the average price is above 3000
5. Sort categories by total revenue (price sum) in descending order