开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜