SQL CASE — Missing ELSE Silently Skips Rows in SUM

Every real-world database holds messy data — status codes instead of human-readable labels, raw numbers that need to be bucketed into categories, columns that need different formatting depending on context, and edge cases that no one anticipated when the schema was first designed. Without a way to apply conditional logic inside a query, you are forced to pull all that raw data into application code just to reshape it into something meaningful. That is slow, it creates a second place where business logic lives, and it means every schema change or new status code requires a code deployment instead of a query change.

The SQL CASE statement solves this by letting you embed if-else logic directly inside SELECT, ORDER BY, WHERE, and even aggregate functions like SUM and COUNT. Instead of making your application do the heavy lifting after the fact, you push the logic down to the database — where the data already lives — and get back exactly the shape you need in a single round-trip.

What most tutorials cover is the basic syntax. What they skip is the failure modes: the missing ELSE that corrupts a revenue total and nobody notices until the CFO asks a question in a board meeting, the ELSE 0 inside a COUNT that inflates every metric on your dashboard, the NULL row that silently vanishes from a JOIN because the CASE expression returned NULL and NULL never equals anything. These are not edge cases you will encounter someday — they are patterns that appear in production code at companies of every size, written by developers who understood the syntax perfectly.

By the end of this article you will know the difference between the two forms of CASE and when each one is appropriate, how to use CASE inside aggregate functions to build pivot-style reports from a single table scan, the exact silent bugs that trip up experienced developers and how to write code that makes them visible rather than hiding them, and the questions interviewers use to probe whether you have actually operated SQL in production or just read about it.

Simple vs Searched CASE — Choosing the Right Form

SQL gives you two syntactically different forms of CASE, and picking the wrong one is the first place developers lose time — not because it throws an error, but because the simple form silently cannot express what you are trying to say.

The simple CASE names one expression at the top and compares it against a list of fixed values using equality. Think of it as a switch statement. You write the column once, and each WHEN clause names a value it might equal. It is concise and easy to scan when every condition is a pure equality check against known constants. Status code translation is its natural habitat: one column, a finite list of possible values, one label per value.

The searched CASE evaluates a completely fresh boolean expression in every WHEN clause. There is no column named up front. Each WHEN can check a different column, use a range operator, call a function, combine multiple conditions with AND and OR, or check for NULL with IS NULL. It is strictly more powerful than the simple form — anything simple CASE can express, searched CASE can also express, but not vice versa.

The practical rule is this: if every one of your WHEN clauses looks like WHEN 'some_fixed_value', use the simple form. The moment any condition needs >, <, BETWEEN, IS NULL, IN with a subquery, or references more than one column, switch to searched CASE. Trying to express a range check inside simple CASE is a common source of confusion because the database interprets WHEN 50 as equality, not as a threshold, and the query compiles without error but returns NULL for every row that is not exactly 50.

One thing that catches people off guard: both forms produce identical execution plans in every major database. The difference is purely about what logic you can express and how readable the result is. There is no performance argument for choosing one over the other — only a correctness and clarity argument.

-- ============================================================
-- SETUP: orders table used throughout all examples
-- ============================================================
CREATE TABLE orders (
    order_id     INT            PRIMARY KEY,
    customer_id  INT            NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status_code  CHAR(1)        -- 'P' = Pending, 'S' = Shipped, 'D' = Delivered, 'C' = Cancelled
                                -- NULL is possible — a workflow bug can leave this unset
);

INSERT INTO orders VALUES
    (1, 101,    25.00, 'P'),
    (2, 102,   340.50, 'S'),
    (3, 103,    12.99, 'D'),
    (4, 104,   980.00, 'D'),
    (5, 105,    55.75, 'C'),
    (6, 106,   150.00, 'S'),
    (7, 107,  3200.00, 'D');

-- ============================================================
-- SIMPLE CASE: one column checked against fixed values
-- Use this when every WHEN is a pure equality check.
-- Perfect for translating status codes to readable labels.
-- ============================================================
SELECT
    order_id,
    total_amount,
    CASE status_code           -- column named ONCE here; each WHEN is a value it might equal
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'         -- guards against future status codes — always include this
    END AS order_status
FROM orders;

-- ============================================================
-- SEARCHED CASE: fresh boolean per WHEN — required for ranges
-- Use this the moment any condition needs >, <, BETWEEN, IS NULL,
-- or touches more than one column.
-- ============================================================
SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount < 50.00                  THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'  -- BETWEEN is inclusive on both ends
        WHEN total_amount >= 500.00                THEN 'Large'
        ELSE 'Uncategorised'   -- catches NULLs and any gap you left in the range logic
    END AS order_tier
FROM orders
ORDER BY total_amount;

-- ============================================================
-- COMMON MISTAKE: trying range logic inside simple CASE
-- The database reads WHEN 50 as status_code = 50, not >= 50.
-- Every row that is not exactly 50 returns NULL — no error.
-- ============================================================
-- SELECT
--     order_id,
--     CASE total_amount
--         WHEN 50  THEN 'Medium'   -- interpreted as total_amount = 50, not >= 50
--         WHEN 500 THEN 'Large'    -- interpreted as total_amount = 500, not >= 500
--     END AS wrong_tier            -- every other row silently returns NULL
-- FROM orders;

CASE Inside Aggregate Functions — The Conditional COUNT Pattern

This is where CASE goes from a label-translation tool to something genuinely powerful — and it is the pattern that separates developers who write reporting SQL from developers who write good reporting SQL.

The problem it solves: you need a dashboard that shows total orders, delivered orders, in-flight orders, delivered revenue, and average large-order value. The naive approach runs five separate queries, each scanning the full table. On a 50-million-row orders table, that is 250 million rows scanned for a single page load. The correct approach runs one query that scans the table once and computes all five numbers simultaneously.

The mechanism is nesting a CASE expression inside an aggregate function. COUNT and SUM both have a relationship with NULL that you can exploit. COUNT ignores NULLs — it only counts non-NULL values. So COUNT(CASE WHEN status_code = 'D' THEN 1 END) returns 1 for delivered rows and NULL (the implicit default when no ELSE is written) for everything else. COUNT counts the 1s and skips the NULLs. You get a delivered-only count from a full-table scan.

For SUM, the pattern is slightly different. Return the actual value you want summed when the condition is true, and 0 when it is not: SUM(CASE WHEN status_code = 'D' THEN total_amount ELSE 0 END). You could also use ELSE NULL here — SUM skips NULLs too — but ELSE 0 makes the intent explicit to the next person reading the query.

The critical asymmetry to burn into memory: ELSE 0 is correct inside SUM but catastrophic inside COUNT. Inside COUNT, 0 is a non-NULL value. COUNT counts it. Every non-matching row contributes 0 to the count, which means COUNT returns the total row count regardless of your condition. The query produces the wrong number with no error and no indication that anything went wrong. This is the single most common CASE bug in production dashboard code, and it is invisible without knowing to look for it.

-- ============================================================
-- GOAL: single-pass dashboard — one query, one table scan,
-- multiple conditional metrics
-- ============================================================
SELECT
    -- Total orders regardless of status
    COUNT(*)                                                          AS total_orders,

    -- COUNT with conditional CASE: no ELSE means non-matches return NULL,
    -- COUNT skips NULLs — only delivered rows are counted
    COUNT(CASE WHEN status_code = 'D' THEN 1 END)                    AS delivered_count,

    -- Multiple conditions in one CASE for in-flight orders
    COUNT(CASE WHEN status_code IN ('P', 'S') THEN 1 END)            AS in_flight_count,

    -- ELSE 0 is correct in SUM — makes intent explicit, handles non-matches cleanly
    SUM(CASE WHEN status_code = 'D' THEN total_amount ELSE 0 END)    AS delivered_revenue,

    -- AVG with conditional CASE: NULL rows are excluded from both
    -- the numerator and the denominator — AVG adjusts automatically
    AVG(CASE WHEN total_amount >= 500.00 THEN total_amount END)       AS avg_large_order_value,

    -- *** THE TRAP — shown here so you recognise it in code review ***
    -- ELSE 0 inside COUNT: every non-matching row still returns 0,
    -- COUNT counts 0 as non-NULL, result = total_orders, not delivered_count
    COUNT(CASE WHEN status_code = 'D' THEN 1 ELSE 0 END)             AS wrong_delivered_count

FROM orders;

-- ============================================================
-- Tier breakdown: CASE inside GROUP BY
-- One query replaces three separate queries with WHERE clauses.
-- In standard SQL you must repeat the CASE expression in GROUP BY;
-- PostgreSQL allows alias references, most other databases do not.
-- ============================================================
SELECT
    CASE
        WHEN total_amount < 50.00                  THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'
        WHEN total_amount >= 500.00                THEN 'Large'
        ELSE 'Uncategorised'
    END                          AS order_tier,
    COUNT(*)                     AS order_count,
    ROUND(SUM(total_amount), 2)  AS tier_revenue,
    ROUND(AVG(total_amount), 2)  AS avg_order_value
FROM orders
GROUP BY
    CASE
        WHEN total_amount < 50.00                  THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'
        WHEN total_amount >= 500.00                THEN 'Large'
        ELSE 'Uncategorised'
    END
ORDER BY tier_revenue DESC;

CASE in ORDER BY — Dynamic Sorting Without Application Code

Most developers know CASE in SELECT. Fewer reach for it in ORDER BY, which is where it solves a problem that comes up constantly in production: sorting rows by a business-defined priority that has nothing to do with alphabetical or numeric order.

The pattern is simple. You write a CASE expression in ORDER BY that maps each business label to a number. Lower number means higher priority. The expression does not need to appear in SELECT at all — it is used purely to control sort order, invisible to the result set and to the application consuming it.

Consider a customer support queue. You want Pending orders at the top because they need action now, then Shipped orders because they are in transit and worth watching, then Delivered as low priority, and Cancelled orders pushed to the bottom since they are closed. Sorting alphabetically on status_code gives you C, D, P, S — exactly backwards from what you want. Sorting on the readable label strings is equally wrong: Cancelled, Delivered, Pending, Shipped. Neither matches the business priority.

Adding a CASE in ORDER BY that maps 'P' → 1, 'S' → 2, 'D' → 3, 'C' → 4 gives you exactly the right order. The CASE expression is computed at query time, costs essentially nothing compared to the I/O of fetching the rows, and is invisible in the output. Any unknown or future status code falls to ELSE 5 and sinks to the bottom rather than appearing at a random position.

The same pattern solves a related problem that trips up cross-database code: NULL sorting position. SQL Server and Oracle sort NULLs last in ascending order by default. PostgreSQL and MySQL sort NULLs first. If you have NULLs in your sort column and your application depends on consistent NULL position across databases, a CASE expression in ORDER BY is the portable fix. Map NULL to 0 to push it first, or to 999 to push it last, regardless of what the underlying database would do by default.

-- ============================================================
-- SCENARIO: support queue — show orders in business priority:
--   1. Pending   — needs action immediately
--   2. Shipped   — in transit, watch for delivery issues
--   3. Delivered — resolved, low urgency
--   4. Cancelled — closed, lowest priority
-- Alphabetical sort on status_code gives C, D, P, S — wrong.
-- Alphabetical sort on the label gives Cancelled first — also wrong.
-- ============================================================
SELECT
    order_id,
    customer_id,
    total_amount,
    status_code,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS order_status
FROM orders
ORDER BY
    -- This CASE is invisible in the result set — used only for sort control
    CASE status_code
        WHEN 'P' THEN 1   -- highest priority: show first
        WHEN 'S' THEN 2
        WHEN 'D' THEN 3
        WHEN 'C' THEN 4   -- lowest priority: show last
        ELSE 5            -- unknown or future statuses sink below Cancelled
    END ASC,
    -- Secondary sort: within the same status, largest orders first
    total_amount DESC;

-- ============================================================
-- PORTABLE NULL HANDLING: force NULLs to a specific position
-- regardless of database-default NULL sort behaviour
-- (PostgreSQL sorts NULLs first ASC; SQL Server sorts them last)
-- ============================================================
SELECT
    order_id,
    status_code
FROM orders
ORDER BY
    CASE WHEN status_code IS NULL THEN 1 ELSE 0 END ASC,  -- NULLs last
    status_code ASC;

Common Mistakes That Create Silent, Hard-to-Debug Bugs

The worst bugs in SQL are not the ones that throw errors — those are easy to find. The dangerous ones return wrong data successfully, with a green status code, and the only evidence that something is wrong is in the numbers themselves. CASE has two classic silent-failure modes that affect developers who have been writing SQL for years, not just beginners.

Missing ELSE and the NULL problem. When none of your WHEN conditions match and you have not written an ELSE clause, SQL does not throw an exception. It returns NULL. If that NULL feeds into a SUM, the row disappears from the total. If it feeds into a JOIN condition, the row vanishes from the result set. If it surfaces in application code, it might cause a NullPointerException in Java, a TypeError in Python, or just an empty string in the UI. In every case, the query that produced it returned HTTP 200 with a successful result. The damage is done quietly.

The correct response to this is to always write an ELSE clause, even in situations where you are confident you have covered every value. If you are covering status codes 'P', 'S', 'D', 'C' and you are certain those are the only four that exist, write ELSE 'UNEXPECTED: ' || status_code anyway. When a new status code ships three months from now — and it will — that sentinel value appears visibly in your output instead of silently producing NULL. A visible anomaly you can diagnose is always better than a silent one you discover at a board meeting.

NULL rows silently fall through in simple CASE. If your column can contain NULL and you write CASE status_code WHEN NULL THEN 'Missing', the WHEN clause will never match. This is not a bug in the database — it is Three-Valued Logic. NULL = NULL evaluates to NULL, not TRUE. CASE only executes a WHEN branch when the condition evaluates to TRUE. NULL is not TRUE, so the branch is skipped and the row falls to ELSE or returns NULL if there is no ELSE.

To explicitly handle NULL rows you must switch to searched CASE and write WHEN status_code IS NULL THEN 'Missing' as its own branch. Place it before the equality checks. CASE stops at the first match, so if a later branch could also match, the NULL check needs to win first. This ordering discipline — NULL check first, narrowest conditions next, broadest condition last — prevents entire classes of silent data errors.

-- ============================================================
-- Add a row with NULL status to expose the silent-NULL failure
-- This simulates a real scenario: a workflow bug leaves the
-- status_code unset for orders created during a system outage
-- ============================================================
INSERT INTO orders VALUES (8, 108, 75.00, NULL);

-- ============================================================
-- BAD: no ELSE, no NULL check
-- Order 8 returns NULL silently — no error, no warning
-- If this column feeds a SUM, order 8 disappears from the total
-- If this feeds a JOIN, the row vanishes from the result set
-- ============================================================
SELECT
    order_id,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        -- No ELSE — order 8 returns NULL here
        -- The query succeeds; the NULL is invisible unless you know to look
    END AS order_status_bad
FROM orders
WHERE order_id = 8;

-- ============================================================
-- ALSO BAD: simple CASE with WHEN NULL — this never matches
-- NULL = NULL evaluates to NULL, not TRUE; the branch is skipped
-- ============================================================
SELECT
    order_id,
    CASE status_code
        WHEN NULL THEN 'Missing'   -- NEVER matches — NULL = NULL is NULL, not TRUE
        WHEN 'P'  THEN 'Pending'
        ELSE 'Other'
    END AS wrong_null_handling
FROM orders
WHERE order_id = 8;
-- Returns: 'Other' — not 'Missing'; the WHEN NULL branch is silently skipped

-- ============================================================
-- GOOD: searched CASE with explicit IS NULL check first,
-- then equality branches, then a sentinel ELSE
-- ============================================================
SELECT
    order_id,
    CASE
        WHEN status_code IS NULL THEN 'Status Not Set'          -- catches NULLs first
        WHEN status_code = 'P'   THEN 'Pending'
        WHEN status_code = 'S'   THEN 'Shipped'
        WHEN status_code = 'D'   THEN 'Delivered'
        WHEN status_code = 'C'   THEN 'Cancelled'
        ELSE 'UNEXPECTED: ' || status_code  -- visible sentinel for future enum values
    END AS order_status_safe
FROM orders;

-- ============================================================
-- PROOF: missing ELSE corrupts SUM — both return the same
-- number here because order 8's status is NULL and is excluded
-- from both variants (SUM skips NULL). The important difference
-- is intent and future-proofing: ELSE 0 explicitly handles
-- non-matching rows; no ELSE relies on NULL being skipped.
-- ============================================================
SELECT
    -- Non-matching rows return NULL (no ELSE); SUM skips them silently
    SUM(
        CASE WHEN status_code IN ('P','S','D','C') THEN total_amount END
    ) AS revenue_no_else,

    -- ELSE 0 makes non-matching rows contribute zero explicitly
    SUM(
        CASE WHEN status_code IN ('P','S','D','C') THEN total_amount ELSE 0 END
    ) AS revenue_with_else

FROM orders;