MySQL - COUNT (DISTINCT) Function

---

---

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity.

MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit.

The MySQL COUNT(DISTINCT) function is used to calculate the number of nun NULL unique values of a particular column.

Syntax

Following is the syntax of this function –

CONCAT(DISTINCT expr)

Example 1

Following is an example demonstrating the usage of this function. Assume we have created a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Socre_In_Exhibiiton_match INT,
	COUNTRY VARCHAR(100),
	RIMARY KEY (ID)
);

This table stores the first and last names, country, scores in an exhibition match of a player. Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', 95, 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', 50, 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 25, 'Sri Lanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', 50, 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', 25, 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', 15, 'India');
insert into MyPlayers values(7, 'James', 'Anderson', 15, 'England');

Following query returns the number of distinct country values in the table –

mysql> SELECT COUNT(DISTINCT country) from MyPlayers;
+-------------------------+
| COUNT(DISTINCT country) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.02 sec)

You can also use the GROUP BY clause along with this function –

mysql> SELECT First_Name, Last_Name, COUNTRY, COUNT(DISTINCT ID) FROM MyPlayers GROUP BY country;
+------------+------------+-------------+--------------------+
| First_Name | Last_Name  | COUNTRY     | COUNT(DISTINCT ID) |
+------------+------------+-------------+--------------------+
| James      |   Anderson |     England |                  1 |
| Shikhar    |     Dhawan |       India |                  4 |
| Jonathan   |      Trott | SouthAfrica |                  1 |
| Kumara     | Sangakkara |    Srilanka |                  1 |
+------------+------------+-------------+--------------------+
4 rows in set (0.08 sec)

Example 2

Following is another example of this function. Assume we have created another table named employee_tbl and inserted records in it as follows –

mysql> CREATE TABLE employee_tbl (
	id INT,
	name VARCHAR(255),
	Work_date INT,
	daily_typing_pages INT
);
mysql> insert into employee_tbl values(1, 'John', DATE('2007-01-24'), 250);
mysql> insert into employee_tbl values(2, 'Ram', DATE('2007-05-27'), 220);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-05-06'), 170);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-04-06'), 100);
mysql> insert into employee_tbl values(4, 'Jill', DATE('2007-04-06'), 220);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-06-06'), 300);
mysql>insert into employee_tbl values(5, 'Zara', DATE('2007-02-06'), 350);

Now, suppose based on the above table you want to count total number of distinct rows in this table, then you can do it as follows −

mysql> SELECT COUNT(DISTINCT name) FROM employee_tbl;
+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

Example 3

Let us create a table named student and inserted records into it using CREATE and INSERT statements as shown below –

mysql> CREATE TABLE student (name VARCHAR(15), marks INT, grade CHAR);
mysql> INSERT INTO student VALUES ('Raju', NULL, 'A');
mysql> INSERT INTO student VALUES ('Rahman', 60, 'B');
mysql> INSERT INTO student VALUES ('Robert', 45, 'C');

Following query prints the number of records that does have the marks values in student table –

mysql> SELECT COUNT(DISTINCT marks) from student;
+-----------------------+
| COUNT(DISTINCT marks) |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

Example 4

Assume we have created and populated a table with name Sales.

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DeliveryTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)
INSERT INTO SALES values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
INSERT INTO SALES values(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
INSERT INTO SALES values(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
INSERT INTO SALES values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
INSERT INTO SALES values(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query is another example of the COUNT() function –

mysql> SELECT COUNT(DISTINCT Price) FROM SALES WHERE Price >5000;
+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)