Hello, coders! 💻
Here's a little challenge for beginners.
We have a users table and we need to know the number of users in 3 age ranges.
- if age < 18 then age range is 'minor'.
- if age < 65 then age range is 'adult'.
- if age >= 65 then age range is 'senior'.
Table structure
CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
age SMALLINT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Sample data
INSERT INTO users(age)
VALUES
(2), (4), (6),
(12), (14), (16), (21), (25),
(27), (31), (33), (37), (39),
(57), (57), (59), (61), (63), (65)
;
Expected results
| ageRange | nb |
| -------- | ------ |
| 6 | minor |
| 12 | adult |
| 1 | senior |
Go ahead and give it a try!
You might want to read about the following statements: IF, CASE/WHEN and GROUP BY.
First we need to determine age ranges and for that we're going to add a CASE statement. Pretty straightforward, right? We could have used two Add that to a Next we need to count how many users belong to each group. And we're done!💡 Click to view my solution 💡
Note that I'm using MySQL but since it is standard SQL you should be able to port it to another DB engine.
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END as ageRange
IF statements but I opted for CASE/WHEN as I find it elegant. :)SELECT statement and you get users' age along with their age group.
It's as simple as adding a GROUP BY ageRange clause.
SELECT
count(*),
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END as ageRange
FROM users
GROUP BY ageRange
Happy coding! ⌨️
Top comments (0)