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.
select type, COUNT(*) from data group by type
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...!!
