Conditional aggregation produces several related metrics in a single grouped query. It is a practical reporting pattern for placement interviews: count completed and failed records, compare revenue categories, or calculate a rate for each customer or period without writing a separate query for every metric.
Key Takeaways at a Glance:
- •Conditional aggregation combines CASE statements inside aggregate functions: SUM(CASE...) or COUNT(CASE...).
- •Pivots rows into reporting metrics in a single table scan (e.g. paid, refunded, and pending revenue).
- •COUNT(CASE WHEN cond THEN 1 END) works because COUNT ignores NULL (the implicit ELSE NULL of CASE).
- •Safely calculate conversion rates and ratios by pairing conditional aggregates with NULLIF to prevent divide-by-zero crashes.
In Simple Words (The Mental Model)
Instead of running 4 separate queries to find total orders, paid orders, pending orders, and cancelled orders, conditional aggregation lets you calculate all 4 numbers in a single query pass. As the database reads each row, it tallies it into the right column bucket.
Real-World Analogy
Think of an accountant with four clicker counters in hand: one for Total Cash, one for Cash from Retail, one for Cash from Online, and one for Refunds. As receipts come in on the conveyor belt, the accountant clicks the relevant counter for each receipt without needing 4 separate conveyor belts.