Ravindra BagaleCourses & study guides Track your progress

8. CASE WHEN: If-Then Logic in SQL

8.3 How to Bucket Amounts (Small / Medium / Large)

By Last updated:

Why. Buckets turn raw money into Small/Medium/Large stories.

What. Chain WHEN amount thresholds from high to low (or low to high carefully).

How. WHEN >= 1000 THEN 'Large' WHEN >= 600 THEN 'Medium' ELSE 'Small'

Steps

  1. Pick thresholds that match the business.
  2. Order WHEN so the first true rule wins.
  3. Count rows per bucket with GROUP BY later.

Examples

Example 1 · dataset: raja_coffee

SELECT
  CASE WHEN amount_inr >= 1000 THEN 'Large'
       WHEN amount_inr >= 600 THEN 'Medium'
       ELSE 'Small' END AS size,
  COUNT(*) AS n
FROM orders
GROUP BY 1
ORDER BY n DESC;

Output

sizen
Medium13
Small7
Large4

Meaning. How many orders in each size bucket.

Ravindra Bagale's Tip

Put the strictest (largest) test first when using >= chains.