mysql 统计次数排序,MySQL对行进行计数和排序

What is the best way to count and sort a column of data in mysql and output the results as a html list?

Example Data:

**Type**

Train

Car

Car

Car

Bus

Bus

Output should be sorted with largest count item first:

- car(3)

- bus(2)

- train (1)

Also, is this bad practice to do on large table?

解决方案

Try this query:

select type, count(*) from vehicles group by type order by count(*) desc

In regards to:

Also, is this bad practice to do on large table?

It depends on your table engine. MyISAM is very fast with aggregate functions like count(*). However, InnoDB has to scan the table and count every time. So I would not recommend count(*)ing a large InnoDB table. Instead you could store a "count" variable in a meta table and update it on inserts/updates only.