sql server - Get the TOP 10 Countries have peoples age 40+ from their DOB ? SQL -

i have table persons have feilds

   pid , pname  , pdateofbirth  , pcountry  

now have 10thousand peoples or above in database

question? want top 10 name of coutries pcountry have persons age 40+?

my effort

 select top 10  count(pid) ratio ,pcountry, datediff(pdob, date.now)   age age >  '40' group country 

what want

      pak = 555       india = 6666       usa= 88       aus = 557 

etc

if understood correctly, want top of countries number of persons aged 40+, grouping should country only.

setup

-- drop table person create table person (     pid int not null identity(1, 1) constraint pk_person primary key,     pdateofbirth date,     pcountry varchar(3) ) go  insert person (pdateofbirth, pcountry) select top 1000 dateadd(day, message_id, '19740101'), 'bel'  sys.messages go  insert person (pdateofbirth, pcountry) select top 1000 dateadd(day, message_id, '19730101'), 'ned'  sys.messages go  insert person (pdateofbirth, pcountry) select top 1000 dateadd(day, message_id, '19760101'), 'deu'  sys.messages go  insert person (pdateofbirth, pcountry) select top 1000 dateadd(day, message_id, '19750101'), 'rom'  sys.messages go  insert person (pdateofbirth, pcountry) select top 1000 dateadd(day, message_id, '19740615'), 'usa'  sys.messages go 

query

declare @today date = getdate() declare @age int = 40  select top 10 pcountry, count(1) cnt person datediff(day, pdateofbirth, @today) >= @age * 365.25 group pcountry order cnt desc 

i have used datediff day option because using year perform difference @ year level only, persons born in 19761201 still included (which not correctly demographically speaking).

also, 365.25 instead of 365 used compensate leap years. however, may still lead 1-day error depending on current date (i think more reasonable when talking age , large data).

[edit]

as correctly pointed out dnoeth, date difference logic may reverted , simplified. so, condition becomes:

where pdateofbirth <= dateadd(year, -40, @today)