I have a table with several rows, for each of them I need to know the most common value.
car boat car car truck truck plane car car
as its values.
I need to know what is the most common value (in this case car).
I have several ideas, but since I must do this for the 30 rows I would like an easy and not CPU intensive query.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
To get a list of values and the number of their appearances:
select col_name, count(col_name) c from table group by col_name order by c desc;
If you want only the most common value:
select col_name, count(col_name) c from table group by col_name order by c desc limit 1;
I wrote the query in a way such that if there are more items with the same highest number of occurrences, you will see them all, not just one of them.
select item from table group by item having count(item) = ( select count(item) as great from table group by item order by great desc limit 1)