SQL display age range

Question

I'm writing a query for age range, in which I want to show the count of people of all age ranges eg

    AGE   PEOPLE
   "0-10"  0
   "11-20" 2
   "21-30" 5
   "31-40" 0
   "41-50" 1

I've tried using

SELECT SUM(CASE WHEN age < 10 THEN 1 ELSE 0 END) AS [Under 10],
        SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS [11-20],
        SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS [21-30]
 FROM people

But it shows ranges as column names

   0-10  11-20 21-30 31-40 41-50
     0     2     5     0    1

which i dont want.

I have also tried GROUP BY but it didn't show the ranges in which the count was 0.

Answer 1

You can use UNION ALL:

SELECT '[Under 10]' as Age, SUM(CASE WHEN age < 10 THEN 1 ELSE 0 END) as People
FROM people
UNION ALL
SELECT '[11-20]', SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END)
FROM people
UNION ALL
SELECT '[21-30]', SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END)
FROM people;

Answer 2

you case when should be like below

CASE WHEN age < 10 then '0-10'
     when age age BETWEEN 11 AND 20 then '11-20'
     when age BETWEEN 21 AND 30 then '21-30'
     ..... end as agegroup,--put here more according to your need
     count(*)
from table group by agegroup