MySQL: Counting Rows that Has Same Value

There are many ways to count rows in a database. Here shows grouping the rows and counting them in MySQL database. The keywords are: "group by" to sort out the rows and "as" keyword to set the value to a variable.

Suppose the database has a table 'species' with 'en' field. The "group by" command will set up just one row for each 'en' value. Along with 'en' value, count(en) value will be retrieved in descending order as being set by "order by" command. The results will be a table with representative 'en' value with the count of it.

SELECT en, count( en ) AS cnt
FROM `species`
GROUP BY en
ORDER BY cnt DESC