Get Distinct Values and Count Them in MySQL

To get distinct values and count them, you can use GROUP BY clause.

The syntax is as follows

select yourColumnName,count(*) as anyAliasName from yourTableName group by yourColumnName;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table GroupByAndCountDemo
   -> (
   -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> ClientName varchar(100)
   -> );
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.16 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.51 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.08 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.47 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.09 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('David');
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Maxwell');
Query OK, 1 row affected (0.17 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Maxwell');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from GroupByAndCountDemo;

The output is as follows

+----------+------------+
| ClientId | ClientName |
+----------+------------+
|        1 | John       |
|        2 | Carol      |
|        3 | Sam        |
|        4 | Sam        |
|        5 | John       |
|        6 | John       |
|        7 | John       | 
|        8 | Sam        |
|        9 | Sam        |
|       10 | Sam        |
|       11 | John       |
|       12 | John       |
|       13 | John       |
|       14 | David      |
|       15 | Maxwell    |
|       16 | Maxwell    |
+----------+------------+
16 rows in set (0.00 sec)

Now let us get distinct values and count them using the following query

mysql> select ClientName,count(*) as TotalCount from GroupByAndCountDemo group by ClientName;

The following is the output

+------------+------------+
| ClientName | TotalCount |
+------------+------------+
| John       |          7 |
| Carol      |          1 |
| Sam        |          5 |
| David      |          1 |
| Maxwell    |          2 |
+------------+------------+
5 rows in set (0.00 sec)