Aggregate with Conditions
Combine aggregates with conditional expressions (CASE/FILTER) to compute multiple metrics in one query - count only delivered orders, sum only paid amounts, or pivot data by category. This is a powerful pattern for analytics.
Conditional Aggregation Patterns
- CASE inside COUNT/SUM: COUNT(CASE WHEN col='val' THEN 1 END)
- FILTER clause (PostgreSQL): COUNT(*) FILTER (WHERE col='val')
- Both produce the same result - FILTER is cleaner in PostgreSQL
- Use multiple conditional aggregates to pivot rows to columns
- SUM(CASE WHEN ... THEN amount ELSE 0 END) for conditional sums
Conditional Aggregate Examples
-- Count orders by status in ONE query (pivot)
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered_revenue
FROM orders;
-- PostgreSQL FILTER clause (cleaner syntax)
SELECT
COUNT(*) AS total_orders,
COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
COUNT(*) FILTER (WHERE status = 'pending') AS pending,
SUM(amount) FILTER (WHERE payment_method = 'credit_card') AS cc_revenue
FROM orders
JOIN payments ON orders.id = payments.order_id;
-- Conditional SUM: revenue only from paid payments
SELECT
SUM(amount) AS total_billed,
SUM(CASE WHEN paid_at IS NOT NULL THEN amount ELSE 0 END) AS collected,
SUM(CASE WHEN paid_at IS NULL THEN amount ELSE 0 END) AS outstanding
FROM payments;
-- Count products in vs out of stock
SELECT
COUNT(CASE WHEN stock > 0 THEN 1 END) AS in_stock,
COUNT(CASE WHEN stock = 0 THEN 1 END) AS out_of_stock
FROM products;Quick Quiz
Tip
Tip
Practice Aggregate with Conditions in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
GROUP BY + HAVING for per-group aggregation.
Common Mistake
Warning
A common mistake with Aggregate with Conditions 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 Aggregate with Conditions 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
- Combine aggregates with conditional expressions (CASE/FILTER) to compute multiple metrics in one query - count only delivered orders, sum only paid amounts, or pivot data by category.
- CASE inside COUNT/SUM: COUNT(CASE WHEN col='val' THEN 1 END)
- FILTER clause (PostgreSQL): COUNT(*) FILTER (WHERE col='val')
- Both produce the same result - FILTER is cleaner in PostgreSQL