I need to find the minimum and average age of students from the birth day

Question

I have a table 'Students' which contains the 'DateOfBirth' of students. I need an SQL statement that I can use in a query to give me the average and minimum ages as fields with those names?I don't want to have to add an age field I just want a SQL statement I can copy and paste that will return me what I need.

 SELECT MIN(DateOfBirth) AS Min Age, AVG(DateOfBirth) AS Avg Age FROM Students;

At the moment all the suggestions I have found ask me to specify a value when I run and I have no idea why?

Answer 1

You should first calculate age from date of birth then find average of age

Select AVG(Datediff("yyyy",DateOfBirth,getdate())) as AVGage from Students
Select MIN(Datediff("yyyy",DateOfBirth,getdate())) as MINage from Students

you can also calucate avg,min in one query

 Select AVG(Datediff("yyyy",DateOfBirth,getdate())) as AVGage , 
        MIN(Datediff("yyyy",DateOfBirth,getdate())) as MINage
        from Students

FOR MS ACCESS DB:

• Now() provides date and time
• Date() provides the date
• Time() provides the time

You can Use Date() Function

 Select AVG(Datediff("yyyy",DateOfBirth,DATE())) as AVGage , 
        MIN(Datediff("yyyy",DateOfBirth,DATE())) as MINage
        from Students

Here is SQLFIDDLE FOR SQLSERVER: