MySQL Conditional Aggregation: SUM with CASE

Conditional aggregation puts a condition inside an aggregate function. One pass over the data gives many numbers.

query.sql

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(10) NOT NULL,
    status VARCHAR(10) NOT NULL,
    amount DECIMAL(8,2) NOT NULL,
    ordered_on DATE NOT NULL
);
INSERT INTO orders (region, status, amount, ordered_on) VALUES
    ('north', 'paid',      100, '2026-04-10'),
    ('north', 'paid',      250, '2026-05-12'),
    ('north', 'cancelled',  80, '2026-05-20'),
    ('south', 'paid',      300, '2026-04-18'),
    ('south', 'refunded',   60, '2026-05-02'),
    ('south', 'paid',      120, '2026-06-03'),
    ('west',  'paid',      500, '2026-06-15'),
    ('west',  'cancelled',  40, '2026-06-16');

Several totals in one query

query.sql

SQL

SELECT COUNT(*) AS all_orders,
       SUM(status = 'paid')                           AS paid_orders,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue,
       SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN amount ELSE 0 END) AS lost_revenue
FROM orders;

Output

OUTPUT

+------------+-------------+--------------+--------------+
| all_orders | paid_orders | paid_revenue | lost_revenue |
+------------+-------------+--------------+--------------+
|          8 |           5 |      1270.00 |       180.00 |
+------------+-------------+--------------+--------------+
1 row in set

A rate

query.sql

SQL

SELECT region,
       COUNT(*) AS orders,
       ROUND(100 * AVG(status = 'paid'), 1) AS paid_percent,
       ROUND(100 * SUM(status IN ('cancelled', 'refunded')) / COUNT(*), 1) AS failed_percent
FROM orders
GROUP BY region
ORDER BY region;

Output

OUTPUT

+--------+--------+--------------+----------------+
| region | orders | paid_percent | failed_percent |
+--------+--------+--------------+----------------+
| north  |      3 |         66.7 |           33.3 |
| south  |      3 |         66.7 |           33.3 |
| west   |      2 |         50.0 |           50.0 |
+--------+--------+--------------+----------------+
3 rows in set

AVG(condition) is a neat way to get a fraction: 1 for true, 0 for false, so the average is the share of true rows.

Pivot: rows to columns

A pivot turns the values of a column into column headings. MySQL has no PIVOT keyword. You write it with conditional aggregation:

query.sql

SQL

SELECT region,
       SUM(CASE WHEN MONTH(ordered_on) = 4 THEN amount ELSE 0 END) AS april,
       SUM(CASE WHEN MONTH(ordered_on) = 5 THEN amount ELSE 0 END) AS may,
       SUM(CASE WHEN MONTH(ordered_on) = 6 THEN amount ELSE 0 END) AS june,
       SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY region
ORDER BY region;

Output

OUTPUT

+--------+--------+--------+--------+--------+
| region | april  | may    | june   | total  |
+--------+--------+--------+--------+--------+
| north  | 100.00 | 250.00 |   0.00 | 350.00 |
| south  | 300.00 |   0.00 | 120.00 | 420.00 |
| west   |   0.00 |   0.00 | 500.00 | 500.00 |
+--------+--------+--------+--------+--------+
3 rows in set

Each region is a row, and each month is a column. The set of columns is fixed in the query. If the months or categories change, the query must change. For a dynamic pivot, build the SQL text in your application or with a prepared statement, with the column names taken from your own trusted list (Prepared Statements).

Unpivot: columns to rows

The opposite is done with UNION ALL:

query.sql

SQL

SELECT region, 'april' AS month, SUM(CASE WHEN MONTH(ordered_on) = 4 THEN amount ELSE 0 END) AS amount FROM orders GROUP BY region
UNION ALL
SELECT region, 'may', SUM(CASE WHEN MONTH(ordered_on) = 5 THEN amount ELSE 0 END) FROM orders GROUP BY region
ORDER BY region, month;

Output

OUTPUT

+--------+-------+--------+
| region | month | amount |
+--------+-------+--------+
| north  | april | 100.00 |
| north  | may   | 330.00 |
| south  | april | 300.00 |
| south  | may   |  60.00 |
| west   | april |   0.00 |
| west   | may   |   0.00 |
+--------+-------+--------+
6 rows in set

Compare periods

query.sql

SQL

SELECT region,
       SUM(CASE WHEN ordered_on < '2026-05-01' THEN amount END) AS before_may,
       SUM(CASE WHEN ordered_on >= '2026-05-01' THEN amount END) AS from_may
FROM orders
WHERE status = 'paid'
GROUP BY region
ORDER BY region;

Output

OUTPUT

+--------+------------+----------+
| region | before_may | from_may |
+--------+------------+----------+
| north  |     100.00 |   250.00 |
| south  |     300.00 |   120.00 |
| west   |       NULL |   500.00 |
+--------+------------+----------+
3 rows in set

Without an ELSE, a row outside the condition gives NULL, which SUM skips. A region with no rows in a period shows NULL, not 0, which tells you “nothing there”. Add ELSE 0 or COALESCE if you want zeros.

Tips

Use SUM(condition) or COUNT(CASE ...) for counts, and SUM(CASE ... THEN amount END) for amounts.
Put the filters that apply to all the totals in WHERE. Put the ones that differ in CASE.
Write the conditions so that ranges do not overlap, and check that the parts add up to the total.
A table of facts (orders) plus a GROUP BY plus conditional sums covers most dashboards.

lightbulb

Check the total

If you pivot into columns, add a SUM(amount) AS total column and make sure it equals the sum of the others. A mismatch means a row fell outside every condition.