CODEWITHSUNDEEP
phpmysqlcount
Adam Fletcher
Adam Fletcher

Reputation: 1045

Search MySQL database, order results by COUNT

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\n<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.

Upvotes: 1

Views: 260

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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

Upvotes: 3

Pallavipradeep
Pallavipradeep

Reputation: 101

check this link if you would like to get details based on username http://sqlfiddle.com/#!2/1201d/4 here i have used type instead you can use username

Upvotes: 1

Related Questions