SQL Data range using Case

user_13253745 0
Viewed 36

I have table1 with data

Age
10
21
35
50

and my query

select count(*) as Total, *
from 

( 
select
case

 when age <18 then '0-20'
 when age between 20 and 29 then '20-29'
 when age between 30 and 39 then '30-39'
 when age between 40 and 49 then '40-49'
 when age between 50 and 59 then '50-59'
 when age between 30 and 39 then '30-39'
 when age between 60 and 99 then '60+'
 end as age_range
 from table1
 ) t
 group by t.age_range

The result

Total   age_range
1       0-20
1       20-29
1       30-39
1       50-59

How do I want to see the result like this ( missing 40-49 with count 0 and above 60+ count 0)

Total   age_range
1        0-20
1        20-29
1        30-39
0        40-49
1        50-59
0        60+

Thank you for your help.

left-join count group-by sql-server sql
2 Answers

You can enumerate the ranges with row_constructor values(), then bring the table with a left join, and finally aggregate:

select count(t.age) total, r.age_range
from (values 
    ( '0-19',  0, 19),
    ('20-29', 20, 29),
    ('30-39', 30, 39),
    ('40-49', 40, 49),
    ('50-59', 50, 59),
    (  '60+', 60, 99),
) r(age_range, low, high)
left join table1 t
    on t.age between r.low and r.high
group by r.age_range
user_106767160

The key to problems like this is to recognize that a SQL query itself cannot really create rows, it can only return a filtered/pivoted/grouped subset of data that is passed in to it. Instead of using a CASE statement we need to turn this into a SET based query where your case options are expressed as rows in a table.

For larger sets you could use a recursive query to build the options, or you could construct a temp table or a table variable to store the rows. However SQL Server 2008 introduced the Table Value Constructor that can be used to quickly create an inline table variable for use in your query, Pinal Dave has a simple writeup on this

-- Existing data
DECLARE @table AS Table(
    age INT
)
INSERT INTO @table (age)
VALUES (10),(21),(35),(50)

-- updated query
select count(age) as Total, AgeRange
from @table t
RIGHT OUTER JOIN (values 
    ( 0,19, '0-19'),
    ( 20, 29, '20-29'),
    ( 30, 39, '30-39'),
    ( 40, 49, '40-49'),
    ( 50, 59, '50-59'),
    ( 60, 999, '60+')
) options(min, max, AgeRange) on t.age BETWEEN options.min AND options.max
GROUP BY AgeRange

Results:

Total       AgeRange
----------- --------
1           0-19
1           20-29
1           30-39
0           40-49
1           50-59
0           60+
user_16902170
Related