SQL Academy · Lesson

Bucketing and Labeling Data

Turn values into categories.

Bucketing and Labeling Data is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is Bucketing?

Bucketing means grouping raw values into named categories — turning numbers, dates, or codes into human-readable labels.

Instead of showing a score of 87, you show "Good". Instead of showing an age of 14, you show "Teen". The CASE expression is your main tool for this.

Sample Table: Orders

We will use an orders table throughout this lesson. Each row has an order amount and a status code.

Run the query below to see the data we are working with.

SELECT id, customer, amount, status
FROM orders
ORDER BY id;

Labeling with CASE WHEN

The simplest bucketing pattern checks a condition and assigns a label. The CASE WHEN ... THEN ... ELSE ... END block returns a new string for each row.

Here we label every order as either 'Large' or 'Small' based on its amount.

SELECT id,
       amount,
       CASE
         WHEN amount >= 500 THEN 'Large'
         ELSE 'Small'
       END AS order_size
FROM orders;

Multiple Buckets with WHEN Chains

You can add as many WHEN clauses as you need. SQL checks them from top to bottom and stops at the first match.

This example splits amounts into three tiers: Small, Medium, and Large.

SELECT id,
       amount,
       CASE
         WHEN amount < 100  THEN 'Small'
         WHEN amount < 500  THEN 'Medium'
         ELSE 'Large'
       END AS order_tier
FROM orders;

Bucketing Text Values

Buckets are not just for numbers. You can match exact text values and replace them with friendlier labels.

Here the raw status codes 'P', 'S', and 'C' become readable words.

SELECT id,
       status,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'C' THEN 'Cancelled'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

Using the Simple CASE Form

When you compare one column against several fixed values, the simple CASE form (CASE column WHEN value ...) is shorter and easier to read than repeating the column name in every WHEN clause.

The result is identical to the searched form.

SELECT id,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'C' THEN 'Cancelled'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

Bucketing Inside GROUP BY

Once you have a bucket label, you can group by it to count how many rows fall into each category. Wrap the whole CASE expression inside GROUP BY.

This query shows how many orders are Small, Medium, or Large.

SELECT
  CASE
    WHEN amount < 100  THEN 'Small'
    WHEN amount < 500  THEN 'Medium'
    ELSE 'Large'
  END AS order_tier,
  COUNT(*) AS total_orders
FROM orders
GROUP BY
  CASE
    WHEN amount < 100  THEN 'Small'
    WHEN amount < 500  THEN 'Medium'
    ELSE 'Large'
  END;

Bucketing with a Subquery Alias

Repeating a long CASE block in GROUP BY is verbose. A cleaner approach wraps the main query in a subquery (or CTE) so you can reference the alias directly.

SELECT order_tier, COUNT(*) AS total_orders
FROM (
  SELECT
    CASE
      WHEN amount < 100  THEN 'Small'
      WHEN amount < 500  THEN 'Medium'
      ELSE 'Large'
    END AS order_tier
  FROM orders
) AS bucketed
GROUP BY order_tier;

Numeric Buckets: Age Groups

Age grouping is a classic bucketing use case. The query below assigns every customer to a named age band.

Notice the conditions go from smallest to largest — SQL stops at the first WHEN that is true, so order matters.

SELECT name,
       age,
       CASE
         WHEN age < 13 THEN 'Child'
         WHEN age < 18 THEN 'Teen'
         WHEN age < 65 THEN 'Adult'
         ELSE 'Senior'
       END AS age_group
FROM customers;

Labeling NULLs in a Bucket

If a value is NULL, no WHEN condition (other than IS NULL) will match it. Without an explicit check, NULLs fall through to the ELSE branch.

Add a WHEN column IS NULL clause to give NULLs their own label.

SELECT id,
       amount,
       CASE
         WHEN amount IS NULL THEN 'No Data'
         WHEN amount < 100   THEN 'Small'
         WHEN amount < 500   THEN 'Medium'
         ELSE 'Large'
       END AS order_tier
FROM orders;

Bucketing in ORDER BY

You can also use a CASE expression directly inside ORDER BY to control sort priority without adding a new column.

The example below always shows Pending orders first, then Shipped, then Cancelled.

SELECT id, customer, status
FROM orders
ORDER BY
  CASE status
    WHEN 'P' THEN 1
    WHEN 'S' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
  END;

Quick Check

Test your understanding of bucketing and labeling data with CASE.

Lesson Recap

Great work! Here is what you learned about bucketing and labeling data:

Bucketing transforms raw values into named categories using CASE WHEN.
Chains of WHEN clauses create multiple buckets; SQL stops at the first match.
The simple CASE form (CASE col WHEN val) is ideal when comparing one column to fixed values.
You can GROUP BY a CASE expression to count rows in each bucket.
Always handle NULL explicitly with WHEN col IS NULL so it gets a meaningful label.
CASE also works inside ORDER BY to define custom sort priority.

Bucketing makes reports far more readable by replacing raw codes and numbers with clear, descriptive labels.

Frequently asked questions

Is the “Bucketing and Labeling Data” lesson free?

Yes — the full text of “Bucketing and Labeling Data” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Bucketing and Labeling Data”?

Turn values into categories. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Bucketing and Labeling Data” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy