Level 6 · Shipping tiers by channel

This practice combines two tools from the chapter: CASE to sort rows into buckets, and conditional aggregation to count each bucket in its own column.

Recap: one column per bucket

GROUP BY gives one row per group. To get the buckets as columns rather than extra rows, count each condition separately:

SELECT channel,
       COUNT(*) AS orders,
       COUNT(CASE WHEN coupon_code IS NOT NULL THEN 1 END) AS coupon_orders
FROM orders
WHERE status = 'completed'
  AND order_date >= '2025-01-01'
  AND order_date < '2026-01-01'
GROUP BY channel;

For 2025 this gives web 256 coupon orders out of 1,761, app 277 out of 1,904 and marketplace 84 out of 630. Each extra condition is one more column, and the table is still read only once, which is why this pattern beats writing one query per bucket and gluing the results together in a spreadsheet.

The CASE returns 1 for rows that match and NULL for the rest, so COUNT counts only matches. SUM(CASE WHEN ... THEN 1 ELSE 0 END) gives the same number and is common too.

The shipping fees on Harbor & Pine orders take few values: 0 when the order qualifies for free shipping, 6.95 standard, 14.95 express and 49.00 freight on web and app, while marketplace orders carry 4.99 or a marked-up fee (7.65, 16.45, 53.90). Ranges in the CASE, rather than exact values, put both price lists into the right tiers.

In 2025 the web channel had 1,761 completed orders, 1,351 of them shipped free (76.7%). Marketplace had 630 and none shipped free.

Common mistakes

• Gaps and overlaps. Write ranges with >= on the lower bound and < on the upper one, so each fee lands in exactly one tier. A quick check: free + standard + express + freight should equal orders.
• Integer percentages. Multiply by 100.0 first, so the division keeps decimals in every SQL dialect.
• Counting the CASE with ELSE 0. COUNT(CASE WHEN ... THEN 1 ELSE 0 END) counts every row, because 0 is not NULL. With ELSE 0, use SUM.

In SQL Server

Conditional aggregation works the same way. Integer division is a real trap there: 100 * free_orders / orders on two integers truncates to a whole number, so keep the 100.0.