Lesson 16.4

Distribution Reports with CASE Buckets in SQL

To see how values spread out, bucket them with CASE and group on the bucket. That gives you a distribution in a few lines.

Individual prices are hard to read in bulk. A distributiongroups them into bands, such as under $10, between $10 and $20, and so on, and counts how many fall in each. The trick: GROUP BY a CASE expression instead of a raw column.

The CASE turns each row's price into a band label; grouping on that label collapses the rows into one per band. To sort the bands in a sensible order, order by min(price) rather than the label text (which would sort alphabetically).

Pattern

select case
         when age < 18 then 'Minor'
         when age < 65 then 'Adult'
         else 'Senior'
       end as age_group,
       count(*) as people
from users
group by age_group

Keep the bands non-overlapping and complete, so every row lands in exactly one bucket. A trailing else catches everything above your last threshold.

Schema · Garden ShopTable · products9 columns · 24 rows

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id intproduct_name textcategory_id intsupplier_id intprice decimalcost decimalquantity_on_hand intreorder_level intdiscontinued bool

Your task

Bucket products into three price bands: 'Under $10','$10 to $19.99', and '$20 and up', and count how many fall in each. Return price_band andproducts, ordered from the cheapest band to the most expensive.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer