Is this query possible 2?

[johnsmith153]

The field in question is a field using the 'date' type,  such as "1967-04-22" and is named 'date_of_birth'

 

(1)

 

I want to get the average age from the date value, but this doesn't seem to work:

 

"SELECT  AVG(calculated_age), DATEDIFF(date_of_birth, NOW()) AS calculated_age FROM table WHERE calculated_age > 21

 

(I also need the WHERE part, but that's not an issue)

 

--

 

(2) I also want the highest age which I'm guessing is the same as above, but using 'SELECT MAX' instead. Is this right?

[Psycho]

You need to take a different approach. I would calculate average of the DATEDIFF (in days) of each record and divide by 365. You can also calculate the highest age using the MIN() date. I also threw in the option for the minimum date. Of course all of the dates int eh calculations must be above 21 years old.

 

SELECT AVG(DATEDIFF(NOW(), date_of_birth))/365 as avg_age,
       DATEDIFF(NOW(), MAX(date_of_birth))/365 as min_age,
       DATEDIFF(NOW(), MIN(date_of_birth))/365 as max_age
FROM table_name
WHERE date_of_birth <= DATE_SUB(NOW(), INTERVAL 21 YEAR)