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?
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:
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: