MySQL Count Query

Forum Moderators: open

Message Too Old, No Replies

MySQL Count Query

resistanceM

11:15 pm on Dec 4, 2007 (gmt 0)

I have a simple table:

Person {firstname, surname, age, hair color}.

How do I query for the most popular firstname?

ive got:


SELECT firstname, count( firstname )
FROM person
GROUP BY firstname

this displays all the firstnames and the number of times it appears which is fine, but i just want the most submitted firstname to appear. any ideas? would appreciate if somebody could direct me into the right direction.
thanks

LifeinAsia

11:24 pm on Dec 4, 2007 (gmt 0)

SELECT firstname, count(firstname)
FROM person
GROUP BY firstname
ORDER BY count(firstname) DESC

Then just display the first record.

resistanceM

11:44 pm on Dec 4, 2007 (gmt 0)

thanks for the reply. when i try that i get:
ERROR: #1111 - Invalid use of group function

I am using mysql version 4.1 would this make a difference?

LifeinAsia

12:57 am on Dec 5, 2007 (gmt 0)

Try count(*) instead of count(firstname) to see if that makes a difference.

resistanceM

9:57 am on Dec 5, 2007 (gmt 0)

thanks for the replies, i overcame it by qualifying the firstname column. had to use "..As firstname".

query works but it only returns one name (Daniel) but 2 other names also equally appear as much. is it going to be complex to return ALL popular names, not just one?

LifeinAsia

4:25 pm on Dec 5, 2007 (gmt 0)

Same query, but just chose the number of results to display depending on how you want to define "most popular" (top 5, all over 40, etc.).

resistanceM

5:35 pm on Dec 5, 2007 (gmt 0)

ofcourse! thanks for your help.

8kobe

10:40 pm on Dec 13, 2007 (gmt 0)

You should probably throw a LIMIT in the query as well. The way one person above does it causes more information then needed to be returned. Limiting will increase the speed of the process