Problem Statement
Explain how CASE and COALESCE help build robust grouped reports when data is messy.
Explanation
CASE lets you route rows into buckets based on business rules, then aggregate each bucket in one scan. This avoids extra joins and keeps logic in the query. You can also map null or out-of-range values into a safe category before you group.
COALESCE substitutes defaults for NULL so aggregates and comparisons behave predictably. For example, you can turn missing amounts into zero for totals, or fill missing categories with “Unknown” so the group appears in the report instead of disappearing.
Code Solution
SolutionRead Only
SELECT COALESCE(category,'Unknown') AS cat,
SUM(CASE WHEN status='PAID' THEN amount ELSE 0 END) AS paid
FROM invoices
GROUP BY COALESCE(category,'Unknown');Practice Sets
This question appears in the following practice sets:
