Combining WHERE + GROUP BY
Combining WHERE and GROUP BY in one query is the most common analytics pattern. WHERE pre-filters individual rows before grouping. GROUP BY then aggregates the filtered subset. Finally HAVING can filter based on aggregate results.
Full Query Order
- 1. FROM — identify the table(s)
- 2. WHERE — filter individual rows
- 3. GROUP BY — group the filtered rows
- 4. HAVING — filter groups by aggregate result
- 5. SELECT — compute output columns (aggregates run here)
- 6. ORDER BY — sort the result
- 7. LIMIT — cut results to n rows
WHERE + GROUP BY Examples
-- Revenue by user for DELIVERED orders only (WHERE pre-filters)
SELECT
u.name,
COUNT(o.id) AS orders,
SUM(o.total_amount) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'delivered' -- filter first
GROUP BY u.id, u.name -- then group
ORDER BY revenue DESC;
-- Top products by total stock (only available products)
SELECT
name,
price,
stock
FROM products
WHERE stock > 0 -- pre-filter
AND price < 1000
ORDER BY stock DESC;
-- Payments grouped by month (only confirmed payments)
SELECT
DATE_FORMAT(paid_at, '%Y-%m') AS month, -- MySQL
SUM(amount) AS monthly_revenue,
COUNT(*) AS payment_count
FROM payments
WHERE paid_at IS NOT NULL -- exclude unpaid
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
HAVING SUM(amount) > 100 -- only months with >$100
ORDER BY month;Quick Quiz
Tip
Tip
Practice Combining WHERE GROUP BY in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
GROUP BY collapses rows by a column — aggregates summarize each group
Common Mistake
Warning
A common mistake with Combining WHERE GROUP BY is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of Combining WHERE GROUP BY from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Combining WHERE and GROUP BY in one query is the most common analytics pattern.
- 1. FROM — identify the table(s)
- 2. WHERE — filter individual rows
- 3. GROUP BY — group the filtered rows