[thelist] mysql count question

Dunstan Orchard dunstan at 1976design.com
Fri Mar 21 16:36:50 CST 2003


Hi there,

I feel that the answer to this should be pathertically simple, but for some 
reason I can't get it to work.

I have a list of names in a single-column db table.
Some of these names occur many times.

eg.

(table = newscite)
NAME
jim
bob
dave
bob
paul
bob
peter
jim
etc...


I want to select these names, count them, order them large-count>small-count, 
then display one instance of each name with its count value next to it.

eg.

bob (12)
jim (7)
pete (7)
dave (3)
paul (1)

What would my query be to do this?
I just can't work it out :o/

I have this SELECT statment which almost does what I want, but it returns 
double of everything...

$query = ("SELECT COUNT(newscite.citename) as count, newscite2.citename as 
name FROM newscite INNER JOIN newscite as newscite2 ON (newscite2.citename = 
newscite.citename) GROUP BY newscite.citename ORDER BY count DESC");

Thanks very much for any help anyone can offer - dunstan

---------------------------
Dorset, England
http://www.1976design.com/
http://www.orchard.it/
http://www.maccaws.org/


More information about the thelist mailing list