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)