Count Number Of User In A Certain Age's Range Base On Date Of Birth

I have table user that has user_id, user_name and user_dob. I want to count how many users that are under 18 year old, 18-50 and over 50. The Age calculation method need to be impr

Solution 1:

Convert the birthdate to a range name, then do a group by over that with count:

selectcasewhen age <18then'Under 18'when age >50then'Over 50'else'18-50'endasrange,
  count(*) as count
from (select DATEDIFF(yy, user_dob, GETDATE()) as age from Customer) c
groupbycasewhen age <18then'Under 18'when age >50then'Over 50'else'18-50'end

By using a subquery to convert the birthdate to a range, the calculation only needs to be performed once per row, so it should perform better. And it's easier to read.

Also, by avoiding UNIONs, the query can be executed in one pass over the table.

Solution 2:

The easiest way to get what you want is:

SELECT'Under 18'AS [Range], COUNT ([user_id]) AS [Count]
  from [user]
  where (DATEDIFF(yy,[user_dob], GETDATE()) <18)
  UNIONALLSELECT'18-50'AS [Range], COUNT ([user_id]) AS [Count] 
  from [Customer]
  where (DATEDIFF(yy,[user_dob], GETDATE()) >=18AND DATEDIFF(yy,[user_dob], GETDATE()) <=50)
  UNIONALLSELECT'Over 50'AS [Range], COUNT ([user_id]) AS [Count] 
  from [Customer]
  where (DATEDIFF(yy,[user_dob], GETDATE()) >50)

But really you should consider other methods, such as grouping.