Conditional aggregation: calculate several metrics in one pass

Problem and reasoning

One scalar subquery per status looks straightforward, but each new metric repeats access to the same table and copied filters can drift. As a report grows, metric definitions become harder to review together.

Conditional aggregation computes several metrics inside one aggregate: matching CASE branches contribute one and other rows contribute zero. Every metric shares the same input set and common WHERE clause, which is easier to verify.

Keep row-count and amount metrics distinct and specify ELSE behavior for NULL. Consolidation is not automatically faster when inputs are tiny or subqueries use very different indexes, so inspect native MySQL plans and rows examined.

Examples

Sample table

Table：orders

orders
status
paid
pending
paid
cancelled

Before / Not recommended

SELECT (SELECT COUNT(*) FROM orders WHERE status = 'paid') AS paid_count, (SELECT COUNT(*) FROM orders WHERE status = 'cancelled') AS cancelled_count;

Before result

result
paid_count	cancelled_count
2	1

After / Recommended

SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count FROM orders;

After result

result
paid_count	cancelled_count
2	1

Why it is better：Both return paid=2 and cancelled=1; the recommended form computes both metrics in one aggregate.

Was this explanation clear?

Keep learning

Continue with related topics or compare database syntax in the SQL dialect translator.