CASE expressions in SQL

CASE is SQL's conditional: the first matching branch wins.

SELECT
    CASE
        WHEN total >= 500 THEN 'large'
        WHEN total >= 100 THEN 'medium'
        ELSE 'small'
    END AS bucket,
    COUNT(*) AS orders,
    SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds
FROM orders
GROUP BY bucket;

How it works

1. Branches are tested in order, ELSE catches the rest.
2. A CASE inside SUM counts rows meeting a condition.
3. You can group by a CASE to bucket rows on the fly.

Keywords and builtins used here

• AS
• BY
• CASE
• COUNT
• ELSE
• END
• FROM
• GROUP
• SELECT
• SUM
• THEN
• WHEN

The run, in numbers

Lines

10

Characters to type

215

Tokens

50

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 32 seconds.

Type this snippet

Step 1 of 7 in Expressions, step 16 of 24 in Analytics & reporting.

← Previous Next →