How to query by decade

My Database has the following columns:
BirthDay, with the oldest person :109years old.
i need to create a chart that will show the birthrate for every 10years.
pls advice
this is my query but is not giving me what is wanted:

select COUNT(BirthDate)  AS BirthYear
From Bio_Data

Who is Participating?
Scott CraigConnect With a Mentor WebmasterCommented:
The reason your check would be different is because DateCreated doesn't come into play in the original query at all.  Do you need to reference DateCreated?

The original query gives you a table of all the decades and how many people were born in those decades.  I'm not sure why you use the DateCreated to check it.  Maybe I don't fully understand your table.
käµfm³d 👽Commented:
What is the type of "BirthDay"?
Scott CraigWebmasterCommented:
If Birthdate is a full formatted date (e.g. 11/12/2011) then, you can use the following:

SELECT COUNT(BirthDate) AS BirthYear, (ROUND(CAST(RIGHT(BirthDate,4) AS INT)/10,0)*10) AS Decade FROM Bio_Data
	GROUP BY (ROUND(CAST(RIGHT(BirthDate,4) AS INT)/10,0)*10)

Open in new window

If it's not a fully formatted date, then you can just change the "RIGHT" formatting to be whatever you need to pull only the year from the date.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

AtourayAuthor Commented:
Error message received:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '1-01' to data type int.

NB: BirthDate is of the form:YYYY-MM-DD
Scott CraigWebmasterCommented:
Just change every instance of RIGHT(BirthDate,4) to LEFT(BirthDate,4)
AtourayAuthor Commented:
Very closs to the answer:
i got the following result
BrithYear        decade
23674-----      1950
27051-----      1990
2737-----      1930
1----      2010
69725----      1970
38-------      1910
9739-----      1940
41794----      1960
106854---      1980
4      ---1900
493-------      1920

when i run this query to check for 2010
      select COUNT(BirthDate) from Bio_Data where Year(DateCreated)= 2010
i got 124170
AtourayAuthor Commented:
This is very fine.
i know why the year 2010 show 1.
that is from my DB.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.