SQL pivot tables: CASE WHEN, COUNT and the traps around NULL

In Excel, I can drag a field into the columns area and get a pivot table almost immediately. SQL can produce a similar layout with a few CASE WHEN expressions. The interesting problems appear afterwards: a shipping method disappears from the summary, an empty cell looks like zero, or the columns no longer add up to the order count. A tidy layout does not prove that the calculation is right.

I will build a report where each source row represents an order and the result shows shipping methods by year. The example uses eight synthetic records in SQLite. I deliberately include a missing method, a new category, a zero amount and unknown amounts. Each assumption becomes visible in the output instead of waiting to surprise someone in a monthly report.

You only need the SQL basics to follow along. Run the blocks in sequence in a new local database. No extension or special PIVOT command is required. Conditional aggregation is useful even when your database also offers dedicated pivot syntax, because it makes the calculations quite explicit.

Decide what each cell should mean

The result rows will represent years, the columns will represent shipping methods and each cell will contain an order count. Those are three separate choices. If I replace the count with a sum of amounts, the layout will look similar, but missing values will need a different explanation. The measure is a central part of the report definition.

Each input record here represents one order, identified by a primary key. If your source contains order items, decide how to reach order level first. Counting rows after joining orders to items can increase the result. Adding DISTINCT without understanding the relationship may conceal a symptom, while leaving its cause unexplained.

Each order in this example has one shipping method. If a real order can generate several shipments using different methods, decide whether the report counts orders, shipments or occurrences of a method. Two technically correct queries can answer different questions when that choice is left open. I settle the meaning before choosing the aggregate.

Prepare data with deliberate edge cases

-- example: fixture
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_year INTEGER NOT NULL,
    shipping_mode TEXT,
    amount INTEGER
);
INSERT INTO orders VALUES
    (1, 2023, 'Standard', 100),
    (2, 2023, 'Standard', NULL),
    (3, 2023, 'Express', 0),
    (4, 2023, NULL, 50),
    (5, 2023, 'Drone', 70),
    (6, 2024, 'Standard', 200),
    (7, 2024, 'Express', 300),
    (8, 2024, 'Express', NULL);

I store the year as an integer so we can concentrate on reshaping the data. In a real table, you will often derive it from a date. The extraction syntax depends on the engine, so I do not present YEAR as a universal command. Also establish whether the report uses the order, shipment or delivery date. Changing that choice can move a record into another period.

The amounts are arbitrary units in one currency, used only for the exercise. Zero means a known zero value; NULL means an unknown amount. There is a separate NULL in the shipping method. A missing category and a missing measure are different problems that may affect different rows. Replacing every missing value with zero would lose that distinction.

Inspect categories in long format

-- example: long
SELECT order_year, shipping_mode, COUNT(*) AS orders_count
FROM orders
GROUP BY order_year, shipping_mode
ORDER BY order_year, shipping_mode;

For 2023, this returns four groups: one order with a missing method, one Drone, one Express and two Standard. For 2024, there are two groups: two Express and one Standard. Five plus three reconciles to the eight source records. At this stage, there is no reason for any shipping method to disappear.

This is long format: category names remain values within a column. It works well for subsequent filtering, charting and feeding BI tools. A new shipping method adds a row without changing the column list. If the recipient plans to build their own pivot, this may be a more useful input than a finished wide table.

Before defining categories, I also inspect their spelling. Standard, STANDARD and Standard followed by a trailing space may represent different source spellings of the same concept. I do not assume that the database will group them according to the business intention. Normalisation is a separate preparation rule, with checks of the values before and after it runs.

CASE chooses a value, then COUNT counts it

-- example: case-rows
SELECT order_id,
       CASE WHEN shipping_mode = 'Standard' THEN 1 END AS marker
FROM orders
WHERE order_year = 2023
ORDER BY order_id;

Orders 1 and 2 receive a marker of one. The other three rows receive NULL. When no CASE condition matches and ELSE is omitted, the expression returns NULL. The CASE documentation describes that behaviour. No counting has happened yet: each input row has simply received an expression result.

COUNT can then count the non-NULL markers. I could return one, the identifier or another value guaranteed to be present. I choose the constant one because it clearly signals the intention to count matching rows. Returning amount instead would exclude a matching order whose amount is unknown. Both the condition and the value after THEN determine what gets counted.

Build a pivot that keeps every order

-- example: pivot
SELECT order_year,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS standard,
       COUNT(CASE WHEN shipping_mode = 'Express' THEN 1 END) AS express,
       COUNT(CASE WHEN shipping_mode IS NULL THEN 1 END) AS unknown_mode,
       COUNT(CASE WHEN shipping_mode NOT IN ('Standard', 'Express') THEN 1 END) AS other_mode,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_year
ORDER BY order_year;

The categories do not overlap and cover all eight records. In each year, the four category counts add up to total_orders. NULL does not satisfy NOT IN, so it has its own IS NULL case. Drone belongs to other methods. Without those two additional columns, the first row would account for only three of its five orders.

I group only by year. Adding shipping_mode to GROUP BY would create a separate result row for each method, defeating the intended wide layout. Assigning each expression a role makes the mechanism easier to follow: year defines the group, CASE selects part of it and COUNT supplies the number. There is no additional pivot magic hidden between those steps.

Why ELSE 0 breaks a conditional COUNT

-- example: count-trap
SELECT
    COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS correct_count,
    COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 ELSE 0 END) AS wrong_count,
    SUM(CASE WHEN shipping_mode = 'Standard' THEN 1 ELSE 0 END) AS alternative_count
FROM orders
WHERE order_year = 2023;

The results are two, five and two. COUNT includes both ones and zeros because neither is NULL. SUM adds them arithmetically, so its version with ones and zeros gives the correct matching-row count here. The aggregate function reference explains the distinction between counting available values and adding them.

The two correct versions behave differently on completely empty input, however. A global COUNT returns zero, while SUM returns NULL. Adding COALESCE determines how that result is presented. Mechanically replacing COUNT with SUM therefore has consequences, particularly in a query that supplies a finished report rather than an intermediate dataset for further investigation.

Order counts and amount totals need different checks

-- example: amounts
SELECT order_year,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS standard_orders,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN amount END) AS known_amounts,
       SUM(CASE WHEN shipping_mode = 'Standard' THEN amount END) AS known_total
FROM orders
GROUP BY order_year
ORDER BY order_year;

For 2023, the result shows two orders, one known amount and a total of one hundred. For 2024, it shows one order, one known amount and a total of two hundred. The first total does not establish that both Standard orders together were worth one hundred. It sums the available amounts. The second amount remains unknown, so I show completeness beside the value.

If all matching amounts are unknown, SUM without ELSE returns NULL. With ELSE 0, the presence of other shipping methods in that group can turn this case into zero. The displayed sum would then depend on records outside the method being analysed. Separate counts of orders and known amounts make the situation much easier to explain than a single ambiguous number.

Consider how the recipient might use the result. Comparing Standard totals across years would be misleading if one year has many missing amounts and the other is complete. A larger available sum could reflect better coverage. I would surface the coverage difference before inviting anyone to interpret it as a change in demand or spending.

A year with no records will not appear by itself

-- example: calendar
WITH years(order_year) AS (VALUES (2023), (2024), (2025))
SELECT y.order_year,
       COUNT(o.order_id) AS total_orders,
       COUNT(CASE WHEN o.shipping_mode = 'Standard' THEN 1 END) AS standard,
       COUNT(CASE WHEN o.order_id IS NOT NULL AND o.shipping_mode IS NULL THEN 1 END) AS unknown_mode
FROM years AS y
LEFT JOIN orders AS o ON o.order_year = y.order_year
GROUP BY y.order_year
ORDER BY y.order_year;

The list of years defines the expected reporting range. LEFT JOIN preserves 2025 even though no order matches it. The result for that year contains three zeros. Counting the order key prevents the row with an empty right-hand side from being treated as an actual order. COUNT(*) would return one in that position.

Missing methods present a similar trap. Testing only whether o.shipping_mode IS NULL would classify the empty side of the join as an order with a missing method. I therefore also require a non-NULL order key. The LEFT JOIN rules explain why that row exists. Review the types of joins before extending this example to additional tables.

There is also a reporting distinction between no orders and no data received. Our exercise defines a complete source with no orders in 2025. In a live pipeline, a failed import could produce an equally empty table. The SQL result alone cannot prove that the business recorded no activity. I would check data availability before giving that zero a business interpretation.

A percentage needs a visible denominator

-- example: share
WITH years(order_year) AS (VALUES (2023), (2024), (2025))
SELECT y.order_year,
       100.0 * COUNT(CASE WHEN o.shipping_mode = 'Standard' THEN 1 END)
       / NULLIF(COUNT(o.order_id), 0) AS standard_share_percent
FROM years AS y
LEFT JOIN orders AS o ON o.order_year = y.order_year
GROUP BY y.order_year
ORDER BY y.order_year;

Standard accounts for 40 percent of orders in 2023 and approximately 33.33 percent in 2024. The 2025 result is NULL because a share of zero orders is undefined. Multiplying by 100.0 preserves fractional calculation. NULLIF turns a zero denominator into NULL, making the intended handling of an empty period explicit.

The denominator includes every order, including missing and other methods. Restricting it to Standard and Express would make the Standard share approximately 66.67 percent in 2023. Both calculations can be executed correctly, but they answer different questions. The metric description needs to identify the population against which the percentage is calculated.

Fixed columns do not make a dynamic pivot

Adding a record with shipping method Locker will not automatically create a locker column. This query has a fixed SELECT expression list. Locker increases other_mode, preserving the reconciliation, but it does not receive separate detail. If every method needs its own column, the output definition must change or be generated through a controlled process.

When categories change frequently, I often prefer long-format output and a pivot in the reporting layer. The recipient can choose which categories to display. Dynamic SQL is not necessary for every analysis. A fixed column set can also be an advantage for an export whose schema is agreed with another system. New categories should then trigger a visible review signal.

Some engines provide other options. PostgreSQL offers crosstab through its tablefunc extension, with its own rules for defining output. We do not run that function in this exercise. Choosing an engine-specific feature still leaves the author responsible for missing categories, amount completeness and the meaning of empty cells.

Watch filters and overlapping categories

Suppose you want only Standard orders while preserving every calendar year. Adding a condition on the right-hand table to WHERE after LEFT JOIN can remove years without matches. The row with an empty right-hand side does not satisfy a comparison with Standard. I place the restriction in the join condition or inside the aggregate, depending on which counts must remain available.

That does not mean every filter should always move to ON. A reporting-year filter concerns the calendar; a method filter concerns orders. I first decide whether I am removing a period from the report or restricting the records counted within its cells. Then I test both an empty year and a year containing only other shipping methods. Those cases reveal the difference quickly.

I also check category overlap. Standard and Express cannot count the same record in this model. Columns for “positive amount” and “Standard method”, however, can overlap. Their sum should not be reconciled to the total order count. Similar table layouts do not imply identical interpretations of their totals.

If the recipient needs overlapping flags, I describe them as independent counts. One record may intentionally appear in several columns. I would not alter the query just to make the sum look tidy. Instead, each flag gets its own condition check, and the combined sum is not presented as the number of unique orders.

Compare periods and choose a useful report size

A year with three orders and a year with five make a convenient syntax exercise, without supporting conclusions about a real market. In a production report, I first check whether the periods are complete. Three months of the current year are not equivalent to twelve months of the previous one. Even correctly calculated shares can reflect seasonality or changes in the available shipping services.

I also ask whether classification stayed consistent. A service launched halfway through a year explains earlier zeros. A simple name change, on the other hand, can create an apparent decline in one category and growth in another. The mapping rule needs documentation, particularly when the report recalculates historical periods after an update.

A wide table works well with a few stable columns. Dozens of methods on a small screen force scrolling and make comparisons difficult. I would consider long format, a category filter or a separate detail view. The number of columns should follow the reader’s task. Being able to add another CASE expression does not establish that the report needs one.

For large source tables, I inspect the execution plan and response time in the actual engine with representative data. This small example checks logic, not performance. Restricting the date range may help when that restriction matches the report definition. I would not promise that one aggregate expression always runs faster than another without measuring it in the relevant environment.

The checks I make before sharing the table

First, I compare the source order count with the sum across all categories for each year. Then I inspect missing and other methods separately. Growth in other methods might indicate a new service or a changed source label. I would show that limitation and agree on classification rather than hide the column because the original request named only two methods.

Next, I add one new method, one missing method and a year without records to a copy of the data. I also test a group with entirely unknown amounts and a group with a known zero amount. Writing down the expected results before execution prevents me from accepting whatever number the database happens to return merely because no error appeared.

Your exercise is to add order nine in 2024 with shipping method Locker and amount 40. That year’s total count should rise to four, other methods should rise to one and the Standard share should fall to 25 percent. The Standard order count and known total should remain unchanged. Explain each change in your own words.

Finally, keep the cell definition, reporting period and missing-value rules beside the query. A reader should know whether a value is an order count, a sum of available amounts or a share of a defined population. A pivot makes comparisons easier once those meanings are clear. A rectangle full of numbers cannot establish them on its own.