âš¡IntermediateAdvanced Functions
Conditional Aggregates and FILTER
Aggregate values conditionally using CASE inside aggregates and the FILTER clause.
Overview
Conditional aggregates let you compute multiple aggregate statistics in a single query by combining aggregates with CASE expressions or the cleaner FILTER clause (PostgreSQL 9.4+). This is the foundation of "pivot" queries turning rows into columns.
For example: COUNT all orders, COUNT only shipped orders, and SUM only high-value orders all in a single GROUP BY query without multiple joins or subqueries.
Key Points
- •COUNT(CASE WHEN cond THEN 1 END) count rows matching a condition
- •SUM(CASE WHEN cond THEN val END) sum only matching rows
- •AGG(col) FILTER (WHERE cond) cleaner, equivalent syntax
- •Useful for pivot-like reports without multiple GROUP BYs
- •FILTER works with any aggregate: COUNT, SUM, AVG, etc.
Examples
Pivot-style conditional aggregates
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS total_orders,
COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
SUM(total) FILTER (WHERE status = 'shipped') AS shipped_revenue,
AVG(total) FILTER (WHERE status = 'shipped') AS avg_shipped
FROM orders
GROUP BY month
ORDER BY month;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4