How to create new bucketed column from an existing continuous column in SQL?

Question

I have the following my_table in SQL with one numeric column:

Age
---
31
53
52
37
57
19
20
63
59

I want to have a new column in the table with range buckets along the following: "Young": 0-30 "MidAge": 31-50 "Old": 51-

So the expected result:

Age AgeGroup
------------
31  MidAge
53  Old
52  Old
37  MidAge
57  Old
19  Young
20  Young
63  Old
59  Old

How can I solve it with SQL?

Answer 1

We can use a CASE expression here:

SELECT Age, CASE WHEN Age <= 30 THEN 'Young'
                 WHEN Age <= 50 THEN 'MidAge'
                                ELSE 'Old'
            END AS AgeGroup
FROM yourTable;

Answer 2

Try this to specify your age ranges

SELECT Age, CASE    
                    WHEN Age BETWEEN 0 and 30 THEN 'Young'
                    WHEN Age BETWEEN 31 and 50 THEN 'MidAge'
                    WHEN Age > 50 THEN 'Old'
                    ELSE 'Unknown'
            END AS AgeGroup
FROM yourTable;

The else part will carter for odd situations incase someone enters a negative value in database