How to Group Rows Into Buckets with Count in a SQL Query
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