How to Count Occurrences of Distinct Values in a MySQL Table?

MySQL query: Count the number of occurrences of different values

In a database table that contains a specific target field, it is often useful to determine the different values ​​in that field and the number of times they occur. To accomplish this task in MySQL, let's delve into a specific scenario:

Suppose we have a table named tablename which contains a field named name. We want to find all distinct name values, count the number of times each name appears, and sort the results in descending order of count.

Sample data:

id	name
1	Mark
2	Mike
3	Paul
4	Mike
5	Mike
6	John
7	Mark

Expected results:

name	count
Mike	3
Mark	2
Paul	1
John	1

To obtain this result we can use the following MySQL query:

<code class="language-sql">SELECT name, COUNT(*) AS count
FROM tablename
GROUP BY name
ORDER BY count DESC;</code>

Explanation:

• SELECT name, COUNT(*) AS count: This part of the query selects the name fields and counts the occurrences of each distinct name. COUNT(*)The function returns the count of all rows in the group.
• FROM tablename: This specifies the table from which data is to be retrieved.
• GROUP BY name: This clause groups the results by the name field, allowing us to count the number of occurrences of each different name.
• ORDER BY count DESC: Finally, this clause sorts the results in descending order based on the number of occurrences.

By executing this query, we get the desired output listing the different name values ​​with their respective counts, sorted by count in descending order.

The above is the detailed content of How to Count Occurrences of Distinct Values in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!