CASE, conditional aggregation and pivot

CASE WHEN … THEN … ELSE … END returns a value according to the first condition that is true. Without an ELSE, whatever does not match becomes NULL.

SELECT id, amount,
       CASE WHEN amount IS NULL THEN 'no amount'
            WHEN amount >= 500 THEN 'high'
            WHEN amount >= 100 THEN 'medium'
            ELSE 'low' END AS band
FROM orders
WHERE id BETWEEN 104 AND 107
ORDER BY id;

id  | amount | band
--- | ------ | ---------
104 | 180    | medium
105 | 1050   | high
106 | NULL   | no amount
107 | 30     | low

The order of the conditions matters: amount >= 100 is also true for 1050, but the first one that matches wins.

Conditional aggregation: one column per status

Summing a CASE counts or adds up only what you care about, in a single pass over the table. It is the portable "pivot", and it runs on any database.

SELECT order_date,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders
GROUP BY order_date
ORDER BY order_date;

order_date | delivered | canceled | pending
---------- | --------- | -------- | -------
2024-06-01 | 2         | 0        | 0
2024-06-02 | 1         | 1        | 0
2024-06-03 | 1         | 0        | 1
2024-06-04 | 2         | 0        | 0
2024-06-05 | 2         | 0        | 0

A rate with the right denominator

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_canceled,
       10 / 4 AS integer_division,
       10 / 4.0 AS real_division
FROM orders;

pct_canceled | integer_division | real_division
------------ | ---------------- | -------------
10           | 2                | 2.5

An integer divided by an integer gives an integer in SQLite and PostgreSQL. Multiply by 100.0 (or convert with CAST) before dividing. In Spark SQL, / always returns a fractional number; integer division is div.

• Spark and Databricks have PIVOT (SUM(amount) FOR status IN ('delivered', 'canceled')), and PySpark has groupBy("order_date").pivot("status").sum("amount"). Passing the list of values avoids an extra read just to discover the columns.