How many orders are small, medium and large?

How many orders are small, medium and large? Small is under 50, medium is 50 to under 100, and large is 100 or more. First give each order its band with CASE, then count the orders in each band. CASE stops at the first rule that fits, so put the smallest band first.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Give each row a band, then count

WITH banded AS (
  SELECT order_id,
         CASE
           WHEN revenue < 50 THEN 'small'
           WHEN revenue < 100 THEN 'medium'
           ELSE 'large'
         END AS size
  FROM orders
)
SELECT size, COUNT(*) AS orders
FROM banded
GROUP BY size

Result:

size	orders
large	3
medium	4
small	5

Step by step

small, medium, large.

WITH banded AS (SELECT order_id, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, COUNT(*) AS orders FROM banded GROUP BY size

No join.

WITH banded AS (SELECT order_id, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, COUNT(*) AS orders FROM banded GROUP BY size

No WHERE.

WITH banded AS (SELECT order_id, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, COUNT(*) AS orders FROM banded GROUP BY size

The bands go in a CTE called banded.

WITH banded AS (SELECT order_id, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, COUNT(*) AS orders FROM banded GROUP BY size

Practice

Try each one in your head first, then open the answer.

1. How many orders are small (under 50), medium (50 to under 100) and large (100 or more)? Show size and orders.

WITH banded AS (SELECT order_id, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, COUNT(*) AS orders FROM banded GROUP BY size

2. What's the total revenue in each size band? Show size and revenue.

WITH banded AS (SELECT revenue, CASE WHEN revenue < 50 THEN 'small' WHEN revenue < 100 THEN 'medium' ELSE 'large' END AS size FROM orders) SELECT size, SUM(revenue) AS revenue FROM banded GROUP BY size

3. How many orders came before 15 February (early), from 15 February to the end of February (mid), and in March (late)? Show period and orders.

WITH banded AS (SELECT order_id, CASE WHEN order_date < '2026-02-15' THEN 'early' WHEN order_date < '2026-03-01' THEN 'mid' ELSE 'late' END AS period FROM orders) SELECT period, COUNT(*) AS orders FROM banded GROUP BY period

4. How many customers spent under 100 (low), 100 to under 200 (mid), or 200 or more (high) in total? Show tier and customers.

WITH spend AS (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id), tiers AS (SELECT customer_id, CASE WHEN total < 100 THEN 'low' WHEN total < 200 THEN 'mid' ELSE 'high' END AS tier FROM spend) SELECT tier, COUNT(*) AS customers FROM tiers GROUP BY tier

5. For each store, how many orders are small (under 50) and how many aren't? Show store, size (small or other) and orders.

WITH banded AS (SELECT store, CASE WHEN revenue < 50 THEN 'small' ELSE 'other' END AS size FROM orders) SELECT store, size, COUNT(*) AS orders FROM banded GROUP BY store, size

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed