Case expression to count multiple groupings

July 20, 2023

I need to group data by its aging. In my sample table, I have 1 column (age) where the numbers range from 1 to 100.. I want to get counts on the number that are <=10, >10, >30 >60 and >90

This is the case expression I used.

select age_bracket, count(*) as nbr  
from 
(
select 
case when age > 90 then 'Nbr >90 days'
when age > 60 then 'Nbr >60 days'
when age > 30 then 'Nbr >30 days'
when age > 10 then 'Nbr >10 days'
when age <=10 then 'Nbr <=10 days'
else 'n/a' end as age_bracket    
from [AGE]
)z
group by age_bracket

These are the results:

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    20
Nbr >30 days    30
Nbr >60 days    30
Nbr >90 days    10

However, this is what I want to see:

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    90
Nbr >30 days    70
Nbr >60 days    40
Nbr >90 days    10

Can this be done using a case expression? If so, how would I structure the expression to return the age bracket column and the values in the Nbr column above?

Any help you can provide would be appreciated.

Here is a sample of the data. 100 rows (value starts at 1 ends at 100)

If you put this in Excel and count how many are

>90 you get 10
>60 you get 40 (60 to 100 = 40)
>30 you get 70 (30 to 100 = 70)
>10 you get 90 (10 to 100 = 90)

>Solution :

You can use where and union results. ie:

select 'Nbr <= 10 days' as age_Bracket, count(*) from [Age] where age <= 10
  union
select 'Nbr > 10 days' as age_Bracket, count(*) from [Age] where age > 10
  union
select 'Nbr > 30 days' as age_Bracket, count(*) from [Age] where age > 30
  union
select 'Nbr > 60 days' as age_Bracket, count(*) from [Age] where age > 60
  union
select 'Nbr > 90 days' as age_Bracket, count(*) from [Age] where age > 90;