getting the most frequent value from mysql table

niehls · 02-17-2003, 09:17 AM

as the header says; i'm trying to get the most frequent value out of a table using php.
say i have a table used for logging user-activity on my homepage.
the table log has a column "action" which contains information on the current activity of the user.

i want to retrieve information on which action is the most frequent, second frequent and so forth.

anybody here know how to achieve this?
//
niehls

Stuka · 02-17-2003, 09:58 AM

You'll need to use the COUNT grouping function - the MySQL docs have a pretty good explanation of how to do it.

madhippy · 02-17-2003, 10:00 AM

something along the lines of

SELECT action, COUNT(action) AS ActionCount
FROM log
GROUP BY action
ORDER BY COUNT(action) DESC;

??

niehls · 02-17-2003, 10:48 AM

thx..i used a php script instead...but now i know for future needs..

dearsina · 01-29-2005, 06:49 AM

Just a quick note for people who might be searching for "most frequent value out of a table " and have found madhippys SQL statement, it's great and works, with one little exception, it should read:

Code:

SELECT action, COUNT(action) AS ActionCount
FROM log
GROUP BY action
ORDER BY ActionCount DESC; //<- ActionCount instead of COUNT(action)

sina
london