[SOLVED] Ordering results by number of occurrences in the database

[FredFredrickson2]

Hi, I've recently been re-working an old site of mine and I really want to add a box on it that displays the most viewed items for the day.  I have a log database that tracks all of the items viewed, which stores the user id of the viewer and the item id that was viewed...  but I can't figure out how my query should be set up to retrieve results from the database in the order that they occur the most.

Basically, I need the query to count how many times each item id is viewed (by counting the occurrences in the log), order them by this, and then return only one result from each ID (using distinct?).  I would really appreciate it if someone could point me in the right direction on this - I can't seem to find anything on Google (probably just a matter of not knowing the correct keywords for the search).  Thanks!

EDIT - I think I solved it.  For anyone who cars to check out / critique my method, here is the query I am using:

SELECT * FROM server_log WHERE date > '$yesterday' GROUP BY item_id ORDER BY count(item_id)

EDIT - Well worked fine on my personal web server, but failed with a 1111 (invalid use of group) once I got it on my web server, and I have no idea why.  I still would really appreciate some help with this, if anyone could give me some insight into the errors of my ways!

[hvle]

Your server probably running and older version, try this:

"select item_id, count(item_id) as total_count from server_log where date > '$yesterday' group by total_count order by total_count"

if doesn't work, post both query and error and I can look into this.

[FredFredrickson2]

Alright, I tried that, and now I get a new error.  Here is the exact query, as I typed it in, and the response from MySQL:

[b]Query:[/b] SELECT item_id, count(item_id) as total_count from server_log GROUP BY total_count ORDER BY total_count

[b]Response:[/b] #1056 - Can't group on 'total_count'

Thanks for taking a look!  :)

EDIT: I just tried substituting the total_count for GROUP BY with the item_id, and it appears to be working, will update if I have further problems, thank you very much for steering me in the right direction!

[hvle]

LOL, my mistake

it should be Group by item_id,
which make more sense right?

so change it to:
SELECT item_id, count(item_id) as total_count from server_log GROUP BY item_id ORDER BY total_count

[hvle]

I forgot to mention if you want the result to sort descending:
SELECT item_id, count(item_id) as total_count from server_log GROUP BY item_id ORDER BY total_count desc

if you want results acsending, leave out the desc or
SELECT item_id, count(item_id) as total_count from server_log GROUP BY item_id ORDER BY total_count asc

[FredFredrickson2]

I got those in there already, but thanks for mentioning it.  Should anyone else be having the same troubles, this thread should serve them well.  Thanks again for all the help!  :)