GROUP BY & HAVING

📌 Definition:

📖 Detailed Explanation:

GROUP BY is how you go from a single overall total to a total per category: orders per user, revenue per country, count per status. Every column in the SELECT that isn't inside an aggregate must appear in GROUP BY (this rule catches many beginners). The distinction between WHERE and HAVING is the classic interview point: WHERE filters individual rows BEFORE grouping, while HAVING filters GROUPS AFTER aggregation. So 'orders over $100' is WHERE, but 'users who placed more than 5 orders' is HAVING COUNT(*) > 5, because that condition is about the group's aggregate, not any single row. Getting this wrong — trying to filter an aggregate in WHERE — produces an error, and trying to filter a raw row in HAVING is inefficient or wrong. For testers, GROUP BY is essential for validating per-category expectations and for finding anomalies like duplicate keys (COUNT(*) > 1 per group).

🔑 Key Points:

• GROUP BY groups rows sharing column values so aggregates apply per group
• Non-aggregated SELECT columns must appear in GROUP BY
• WHERE filters rows BEFORE grouping; HAVING filters groups AFTER aggregation
• Aggregate conditions (COUNT(*) > 5) belong in HAVING, not WHERE
• GROUP BY ... HAVING COUNT(*) > 1 is the standard duplicate-finding pattern

🌍 Real-World Example:

📎 Code Example:

-- Orders and revenue per country (row filter first, then group)
SELECT u.country, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'confirmed'      -- WHERE: filters rows before grouping
GROUP BY u.country
HAVING SUM(o.total) > 1000        -- HAVING: filters groups after aggregation
ORDER BY revenue DESC;

-- Find customers who placed more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;