搜索MySQL数据库，按COUNT排序结果

Question

I have a database table 'sales_list'. In this is rows of sales records attributed to a users_sales_guild_id. I'd like to query the table and order results by the number of sales made by each user, highest to lowest.

I thought this query would do it, but alas no...

$total_query = "SELECT *, COUNT(users_sales_guild_id) AS users_sales_guild_id_count FROM sales_list WHERE sales_entry_date BETWEEN '2013-10-01 00:00:00' AND '2014-11-30 23:59:59' ORDER BY users_sales_guild_id_count DESC";
$total_rs = mysql_query($total_query) or trigger_error ("Query: $total_query
<br>MySQL Error: " .@mysql_error()); // Run the query.
$num_rs = mysql_num_rows($total_rs);

This query returns 1 record. rather than a selection of records ordered by the number of sales by each user.

Your assistance is much welcomed.

Answer 1

count(*) will return one row unless there is a group by clause, so the query should be as

SELECT *, 
COUNT(*) AS users_sales_guild_id_count 
FROM sales_list 
WHERE sales_entry_date BETWEEN '2013-10-01 00:00:00' AND '2014-11-30 23:59:59' 
group by users_sales_guild_id
ORDER BY users_sales_guild_id_count DESC

UPDATE : Its better to select col1,col2 ..... instead of * while doing group by - Point raised by InoSHeo

check this link http://sqlfiddle.com/#!2/1201d/6