1 · Reporting patterns and edge cases · 25 MIN

Conditional aggregation in one grouped report

Conditional aggregation in one grouped report

Conditional expressions let one grouped query compute several related measures without duplicating the source scan. COUNT(*) counts every row, while COUNT(amount) excludes NULL amounts. SUM(CASE...) makes a condition explicit and includes a zero for nonmatching rows. Define whether unknown amounts belong in a denominator before reporting a percentage. This exercise returns counts rather than silently treating missing amounts as zero spending.

Define the output row grain and ordering before choosing SQL operators.

Read the example

SELECT category, COUNT(*), COUNT(amount), SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) FROM events GROUP BY category ORDER BY category;

[["food",3,3,3],["travel",2,1,0]]

Your challenge

Return category, total row count, non-NULL amount count and number of amounts at least 5, ordered by category.

Solution cost: Aggregation scans input; window ordering can add O(n log n) sorting. Inspect the database plan. time · Grouping or window buffers depend on the plan and partition size. space

Common trap

COUNT(amount) and COUNT(*) answer different questions when NULL exists.

Further reading: SQLite window functions