CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Ronaldinho Learn Coding
Ronaldinho Learn Coding

Reputation: 13824

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 improved to calculate exact age but for now I am more interested in finding the method to count

So I tried:

SELECT COUNT ([user_id]) 
from [user]
where (DATEDIFF(yy,[user_dob], GETDATE()) < 18)
UNION ALL
SELECT COUNT ([user_id]) 
from [user]
where (DATEDIFF(yy,[user_dob], GETDATE()) >= 18 AND DATEDIFF(yy,[user_dob], GETDATE()) <=50)
UNION ALL
SELECT COUNT ([user_id]) 
from [user]
where (DATEDIFF(yy,[user_dob], GETDATE()) > 50)

It gives me result like:

(No column name)
1218
3441
1540

But I need something like this

Range    | Count
----------------
Under 18 |  1218
18-50    |  3441
Over 50  |  1540

Any suggestions how to archive above format?

Upvotes: 1

Views: 6148

Answers (2)

Bohemian
Bohemian

Reputation: 424983

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

select
  case when age < 18 then 'Under 18'
       when age > 50 then  'Over 50'
       else  '18-50' end as range,
  count(*) as count
from (select DATEDIFF(yy, user_dob, GETDATE()) as age from Customer) c
group by case when age < 18 then 'Under 18'
   when age > 50 then  '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.

Upvotes: 9

Amit
Amit

Reputation: 46323

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)
  UNION ALL
  SELECT '18-50' AS [Range], COUNT ([user_id]) AS [Count] 
  from [Customer]
  where (DATEDIFF(yy,[user_dob], GETDATE()) >= 18 AND DATEDIFF(yy,[user_dob], GETDATE()) <=50)
  UNION ALL
  SELECT '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.

Upvotes: 1

Related Questions