How to Count Distinct Values and Their Occurrences in MySQL?

Finding Distinct Values and Counting Their Occurrences in MySQL

In MySQL, counting the occurrences of distinct values and ordering the results by count is a common requirement for data analysis and aggregation. To accomplish this, we can leverage the power of SQL's aggregation and sorting capabilities.

To count the occurrences of distinct values, we can employ the COUNT() aggregate function along with the GROUP BY clause. The GROUP BY clause groups the rows by a specified field, while COUNT() returns the count of unique instances within each group.

Consider the following MySQL query:

SELECT name, COUNT(*) AS count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

Explanation:

• SELECT name, COUNT(*) AS count: Retrieves the name field and calculates the count of distinct name values for each group. The alias "count" is used for the count value for clarity.
• FROM tablename: Specifies the table to retrieve data from.
• GROUP BY name: Groups the rows by the name field. This ensures that we count the occurrences of each distinct name.
• ORDER BY count DESC: Orders the results in descending order based on the count of distinct values.

Example:

Suppose you have a table named "people" with the following data:

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

Executing the above query on this table would produce the following output:

name	count
Mike	3
Mark	2
Paul	1
John	1

This result shows that Mike appears three times, Mark appears twice, Paul appears once, and John appears once. The names are ordered in descending order based on their occurrence count.

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