Group by named column
I always forget how to do things like this.
开发者_StackOverflow社区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?
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( ) )
Nevermind, I figured it out.
Ok, I need to use COUNT(*)
SELECT TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age, COUNT( * )
FROM person
GROUP BY age
精彩评论