How Can I Count Distinct Values and Their Occurrences in MySQL?

Count unique values ​​and their occurrence times in MySQL

Counting unique values ​​and their occurrences is a common task in data processing. In MySQL you can achieve this with a simple query:

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

Instructions:

• SELECT name, COUNT(*) AS count: Select the name column and use COUNT(*) to count its occurrences (number of occurrences). The results are stored in the count column.
• FROM tablename: Specify the MySQL table to extract data from. Replace tablename with the actual table name.
• GROUP BY name: Group results by name column. This ensures that you only count occurrences of unique name values.
• ORDER BY count DESC: Sort the results in descending order of the count column, displaying the most frequently occurring name value first.

Example:

Consider the following example data:

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

Running a query on this data will produce the following results:

name	count
Mike	3
Mark	2
Paul	1
John	1

This query concisely and efficiently counts the number of occurrences of each unique name in the table, and sorts the results in descending order by the number of occurrences, allowing users to quickly understand the data distribution.

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