How to get the count of each different value in a MySQL column?

Let's look at an example of getting the count of each different value in a column. First, we will create a table.

The CREATE command is used to create a table.

mysql> create table DistinctDemo1
　　　-　> (
　　　-　> id int,
　　　-　> name varchar(100)
　　　-　> );

Insert records

mysql> insert into DistinctDemo1　values(1, 'John');
mysql> insert into DistinctDemo1　values(2, 'John');
mysql> insert into DistinctDemo1　values(3, 'John');
mysql> insert into DistinctDemo1　values(4, 'Carol');
mysql> insert into DistinctDemo1　values(5, 'David');

Display all records

mysql> select　*from DistinctDemo1;

The following is the output to display all records.

+------+-------+
| id | name |
+------+-------+
|　　　　1　| John |
|　　　　2　| John |
|　　　　3　| John |
|　　　　4　| Carol |
|　　　　5　| David |
+------+-------+
5　rows in set (0.00 sec)

The following is the syntax for getting the count.

mysql> SELECT name,COUNT(1) as OccurenceValue FROM DistinctDemo1　GROUP BY name ORDER BY OccurenceValue;

This is the output.

+-------+----------------+
| name | OccurenceValue |
+-------+----------------+
| Carol |　　　　　　　　　　　　　　1　|
| David |　　　　　　　　　　　　　　1　|
| John |　　　　　　　　　　　　　　3　|
+-------+----------------+
3　rows in set (0.04　sec)