SQL Count rows and display in order

Question

Let's say i have a table named animals with with columns named "id" and "name" and "type". The last column is filled with cow, chicken, horse, pig, elephant, hippo etc.

What i want is a query that counts the number of types and displays them in order like this...

chicken 45
cows 40
horse 5
etc...

Now i only want to show the 10 with the highest amount. I use this query...

$result = mysql_query("SELECT * FROM animals ORDER BY id DESC LIMIT 0, 10");

while($row = mysql_fetch_array($result))
{

echo "<tr><td>" . $row['type']. "</td><td align=\"right\"></td></tr>";
}

The code above shows only the types like

horse
chicken
chicken
cow
chicken
cow
cow
horse
etc...

I don't know how to use the counter and sort in highest value.

possibly duplicate stackoverflow.com/questions/12789396/…

Muhammad
– Muhammad

2013-12-10 09:11:59 +00:00
Commented Dec 10, 2013 at 9:11 — Muhammad
– Muhammad, Commented Dec 10, 2013 at 9:11

Kumar Saurabh Sinha · Accepted Answer · 2013-12-10 09:10:46Z

1

Please Try the following query:

Select type, count(type) as type_count FROM animals GROUP BY type ORDER BY type_count desc LIMIT 0, 10

answered Dec 10, 2013 at 9:10

Kumar Saurabh Sinha

8708 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

uvais · Accepted Answer · 2013-12-10 09:18:36Z

0

try this :

select name, count(name) as total from animals
 group by name order by total desc limit 10

edited Dec 10, 2013 at 9:18

answered Dec 10, 2013 at 9:12

uvais

4162 silver badges6 bronze badges

Comments

TheHippo · Accepted Answer · 2013-12-10 09:48:24Z

0

Try:

select 
    top 10 type, Count(*) 
from 
    animals 
group by 
    type 
order by 
    count(*) desc,type

edited Dec 10, 2013 at 9:48

TheHippo

63.3k15 gold badges77 silver badges101 bronze badges

answered Dec 10, 2013 at 9:14

Krishna

1708 bronze badges

Collectives™ on Stack Overflow

SQL Count rows and display in order

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related