MySQL Count and sort rows

Question

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?

Answer 1

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.

Answer 2

select type, COUNT(*) from data group by type

Answer 3

Try this query:

select t.type, count(t.type) as typecount from type as t group by type order by typecount desc

Hope help this query...!!