sql - Count number of user in a certain age's range base on date of birth -

i have table user has user_id, user_name , user_dob.

i want count how many users under 18 year old, 18-50 , on 50.

the age calculation method need improved calculate exact age more interested in finding method count

so tried:

select count ([user_id])  [user] (datediff(yy,[user_dob], getdate()) < 18) union select count ([user_id])  [user] (datediff(yy,[user_dob], getdate()) >= 18 , datediff(yy,[user_dob], getdate()) <=50) union select count ([user_id])  [user] (datediff(yy,[user_dob], getdate()) > 50) 

it gives me result like:

(no column name) 1218 3441 1540 

but need this

range    | count ---------------- under 18 |  1218 18-50    |  3441 on 50  |  1540 

any suggestions how archive above format?

convert birthdate range name, group on count:

select   case when age < 18 'under 18'        when age > 50  'over 50'        else  '18-50' end range,   count(*) count (select datediff(yy, user_dob, getdate()) age customer) c group case when age < 18 'under 18'    when age > 50  'over 50'    else  '18-50' end 

by using subquery convert birthdate range, calculation needs performed once per row, should perform better. , it's easier read.

also, avoiding unions, query can executed in 1 pass on table.