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

Question

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?

Bohemian · Accepted Answer · 2016-01-16T03:08:04.173

9

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.

edited Jan 16 '16 at 03:08

answered Jan 16 '16 at 02:47

Bohemian

412,405
93
575
722

Error: `Each GROUP BY expression must contain at least one column that is not an outer reference.`? – Ronaldinho Learn Coding Jan 16 '16 at 02:56
@RonaldinhoLearnCoding try using explicit group by expression (see edit) – Bohemian Jan 16 '16 at 03:08

score 1 · Answer 2 · edited May 23 '17 at 12:16

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.

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

2 Answers2

Linked