How to Group Rows Into Buckets with Count in a SQL Query

Published Nov 11, 2022

How can we group rows returned from a SQL query into buckets with the count in each bucket?

Example grouping with bucket counts

Suppose we have a table of people with name and age columns.

name age
John 4
Jane 19
Bob 21
George 29
Jack 31

We want to group people into categories and get the number of people in each category.

category count
Below 20 2
Between 20 and 30 2
Above 30 1

SQL query to group rows into buckets with counts

We can use a simple CASE expression to bucket rows based on certain column values.

SELECT
  CASE
    WHEN age >= 0 AND age <= 20 THEN 'Below 20'
    WHEN age > 20 AND age <= 30 THEN 'Between 20 and 30'
    WHEN age > 30 THEN 'Above 30'
  END category,
  count(*) AS count
FROM tableName
GROUP BY 1