MySQL Query to get count of unique values?

To count the unique values on a column, you need to use keyword DISTINCT. To understand how it is done, let us create a table. The query to create a table is as follows −

mysql> create table UniqueCountByIPAddress    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserHits int,    -> UserIPAddress varchar(50),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.69 sec)

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

mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(10,'127.0.0.1'); Query OK, 1 row affected (0.20 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(10,'127.0.0.1'); Query OK, 1 row affected (0.20 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(20,'127.0.0.1'); Query OK, 1 row affected (0.20 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(20,'127.0.0.1'); Query OK, 1 row affected (0.17 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(20,'127.0.0.2'); Query OK, 1 row affected (0.13 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(20,'127.0.0.2'); Query OK, 1 row affected (0.20 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(30,'127.0.0.2'); Query OK, 1 row affected (0.17 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(30,'127.0.0.2'); Query OK, 1 row affected (0.28 sec) mysql> insert into UniqueCountByIPAddress(UserHits,UserIPAddress) values(30,'127.0.0.2'); Query OK, 1 row affected (0.14 sec)

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

mysql> select *from UniqueCountByIPAddress;

The following is the output −

+----+----------+--------------+ | Id | UserHits | UserIPAddress| +----+----------+--------------+ |  1 |       10 | 127.0.0.1 | |  2 |       10 | 127.0.0.1 | |  3 |       20 | 127.0.0.1 | |  4 |       20 | 127.0.0.1 | |  5 |       20 | 127.0.0.2 | |  6 |       20 | 127.0.0.2 | |  7 |       30 | 127.0.0.2 | |  8 | 30 | 127.0.0.2 | |  9 | 30 | 127.0.0.2 | +----+----------+--------------+

The following is the query to count the distinct value on column ‘UserIPAddress’ −

mysql> select UserHits,count(distinct UserIPAddress) as TotalHits -> from UniqueCountByIPAddress group by UserHits;

The following is the output −

+----------+-----------+ | UserHits | TotalHits | +----------+-----------+ | 10 | 1 | | 20 | 2 | | 30 | 1 | +----------+-----------+ 3 rows in set (0.05 sec)