How to fetch how many times a value is, or has been repeated, in a column in MySQL?

[Ben Stones]

I was wondering how to do this for ages. Yesterday evening I decided to try and fix the problem of trying to find how to ORDER the results in a column based on both how many times it has been repeated and the the highest repeat count. And here it is:

$get_popular_polls = $this->ci->db->query("SELECT poll_id,user_id,COUNT(poll_id) AS popular_polls_count FROM poll_votes GROUP BY poll_id ORDER BY popular_polls_count DESC LIMIT ".$this->ci->db->escape($limit)); 

This is part of an application I am developing in my spare time, if you're wondering.

What's happening here?

As always, I think an explanation is necessary and always helps people out. The AS popular_polls_count essentially creates a "new column" by the specific name in the results that are returned by the query that will return the COUNT of the specific row. Take this example:

SELECT poll_id,user_id,COUNT(poll_id) AS popular_polls_count FROM poll_votes

However, it will only return the COUNT of many rows there are in the specific column (NULL values don't get included here), as shown below (and hence only one row result).



How do you make it so you can return based on the highest COUNT? Well, logically, you could do this:

SELECT poll_id,user_id,COUNT(poll_id) AS popular_polls_count FROM poll_votes ORDER BY popular_polls_count DESC

But this does nothing different - you are only simplying adding an ORDER BY clause. Hence, it will only return one row with the amount of rows in the column poll_id. To fix this, you need to add a GROUP BY clause before the ORDER BY clause, as shown in the original code:

SELECT poll_id,user_id,COUNT(poll_id) AS popular_polls_count FROM poll_votes GROUP BY poll_id ORDER BY popular_polls_count DESC

This means repeated values in poll_id are not displayed in the output as a result of GROUP BY, and so the COUNT is being applied to each GROUP BY row.

Hope this helps .