🟡 GROUP BY & HAVING

GROUP BY & HAVING: GROUP BY: imagine a large laundry service — hundreds of shirts arrive from different customers, you first sort each customer's shirts into separate piles (GROU

GROUP BY: imagine a large laundry service — hundreds of shirts arrive from different customers, you first sort each customer's shirts into separate piles (GROUP BY customer_id), then count each pile (COUNT), then push forward only customers with more than 5 shirts (HAVING COUNT > 5). Where does WHERE fit in this process? Before HAVING — you discard torn shirts before they even enter the sorting floor (WHERE condition). In Java you'd write `list.stream().collect(Collectors.groupingBy(t -> t.environment, Collectors.counting()))` and filter the result separately; SQL's GROUP BY + HAVING does both in a single query. The distinction is critical: WHERE runs before grouping and cannot access aggregate results; HAVING runs after grouping and can filter on COUNT/SUM/AVG. For a QA engineer misunderstanding this order leads to 'I used WHERE instead of HAVING and got zero results' — and when a query silently returns zero rows without an error message, the risk of a false PASS reaches its peak.

GROUP BY groups rows with the same value in a column. HAVING filters those groups (like WHERE but for aggregate results). You CANNOT use COUNT/SUM/etc. in a WHERE clause — use HAVING instead.

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?