是否可以在一个值上计算所有行，并获取具有最大计数（mysql）的列名？

Question

I want to count all rows base on the category column, example below, category apple has two rows, so get the the category name apple. Thank you.

id  user_id   category
1    2         apple
2    4        banana
3    6         apple
4    7        berry

//Count all rows, apple has two row, the largest count, so then get the the category name apple

Answer 1

Use Group by to count the category.

Then order the result set in descending order of the count and select the top 1.

Query

select t.category
from 
 (
    select category,
    count(category) as cat_count
    from fruits
    group by category
 )t
 order by t.cat_count desc limit 1;

SQL Fiddle

If multiple category having same max count. Then,

Query

select t.category
from
(
   select category,
   count(category) as cat_count
   from fruits
   group by category
)t
where t.cat_count = 
(
   select count(category) as cat_count
   from fruits
   group by category
   order by count(category) desc
   limit 1
);

SQL Fiddle