How can we count a number of unique values in a column in MySQL table?

By using DISTINCT keyword along with column name as the argument of COUNT() function we can count the number of unique values in a column. The syntax is as follows −

SELECT COUNT(DISTINCT Col_name) FROM table_name;

Example

Suppose we have the following table

mysql> Select * from tender;
+----------+--------------+--------------+-------+
| clientid | client_Fname | Client_Lname | value |
+----------+--------------+--------------+-------+
|   100    | Mohan        | Kumar        | 60000 |
|   101    | Sohan        | Singh        | 50000 |
|   101    | Somil        | Rattan       | 55000 |
|   103    | Gaurav       | Kumar        | 75000 |
|   103    | Rahul        | Singh        | 63000 |
+----------+--------------+--------------+-------+
5 rows in set (0.00 sec)

Now if we want to count the total number of unique values in the column named ‘clientid’ then it can be done with the help of the following query −

mysql> Select COUNT(DISTINCT Clientid) from tender;
+--------------------------+
| COUNT(DISTINCT Clientid) |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

And if we want to count the total number of unique values in column named ‘Client_Lname’ then it can be done with the help of following query −

mysql> Select COUNT(DISTINCT Client_Lname) from tender;
+------------------------------+
| COUNT(DISTINCT Client_Lname) |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.00 sec)

Ankitha Reddy

Updated on: 30-Jul-2019

163 Views

Kickstart Your Career

Get certified by completing the course

Get Started