Group by named column

Question

I always forget how to do things like this.

I have a database table with birthdates and I want to find out how many people have the same age.

I'm trying:

SELECT TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age, COUNT( age ) 
FROM  person 
GROUP BY age

but get the error

Unknown column 'age' in 'field list'

How can I do a group by on a named column?

Answer 1

Nevermind, I figured it out.

Ok, I need to use COUNT(*)

SELECT TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age, COUNT( * ) 
FROM  person 
GROUP BY age

Answer 2

Aliases can't be used in group by clauses, nor can it be used in other columns in the select list (yes, this sucks).

SELECT TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age, COUNT( TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ))
FROM  person
GROUP BY TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) )