MySQL ORDER BY COUNT()?

Question

I have a table setup in my database with this structure:

I have running a query through a while loop and I want to order by the count of the prof column.

This is what my query currently looks like, though I keep getting errors.

$order_list = mysql_query("
    SELECT COUNT(prof), 
    FROM prof_rating 
    ORDER BY COUNT(prof) ASC");

This is the warning I keep getting.

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in

Answer 1

For what it's worth, any use of an aggregate function in the select-list means that the result set will have only one row. It makes little sense to sort a results set with a single row.

If you meant to get a count of ratings per distinct value of prof, you should use this:

$order_list = mysql_query("
    SELECT prof, COUNT(*) AS PROFCOUNT, 
    FROM prof_rating 
    GROUP BY prof
    ORDER BY PROFCOUNT ASC'");

That will output multiple rows, one row per prof value, with the count of rows for each given prof value.

Answer 2

Alias the column name and then put that in your order by clause :)

$order_list = mysql_query("
SELECT COUNT(prof) AS PROFCOUNT, 
FROM prof_rating 
ORDER BY PROFCOUNT ASC'");