SQL COUNT DISTINCT

Introduction to SQL COUNT DISTINCT

The following article provides an outline for SQL COUNT DISTINCT. We can find out the number of records for values of the resultset of the particular query in SQL using the count function. Further, there can be redundancy which means repetition of the same values in the particular result set. To retrieve the unique values from the result set of the particular query statement’s output, we can make the use of distinct functions in SQL. We can use both the functions count and distinct togetherly to find out the number of records with distinct values from the resultset. Using both the functions togetherly can prove helpful in many situations in real-time case scenarios, such as retrieving the count of the grouped values having distinct records in them.

Syntax:

COUNT ([DISTINCT] expression_or_value);

The expression_or_value can be any of the column names of expression involving other aggregated functions, and so that will lead to retrieval of many values that might have duplicate values in them.

To get unique values from the records, we will use the DISTINCT keyword before the expressions and finally, to get only the number of records of those distinct values, we can use the COUNT function that will wrap the contents.

Example of SQL COUNT DISTINCT

Consider one example; we will create one table named workers with the help of the following CREATE TABLE statement in SQL.

Code:

CREATE TABLE `workers` (
`developer_id` INT(11) NOT NULL,
`team_id` INT(11) NOT NULL,
`name` VARCHAR(100) DEFAULT NULL,
`position` VARCHAR(100) DEFAULT NULL,
`technology` VARCHAR(100) DEFAULT NULL,
`salary` INT(11) DEFAULT NULL
)

Let us insert some records in the table workers with the help of the following INSERT query statement.

Code:

INSERT INTO `workers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES
(1, 1, 'Payal', 'Developer', 'Angular', 30000),
(2, 1, 'Heena', 'Developer', 'Angular', 10000),
(3, 3, 'Vishnu', 'Manager', 'Maven', 25000),
(4, 3, 'Rahul', 'Support', 'Digital Marketing', 15000),
(5, 3, 'Siddhesh', 'Tester', 'Maven', 20000),
(6, 7, 'Siddharth', 'Manager', 'Java', 25000),
(7, 4, 'Brahma', 'Developer', 'Digital Marketing', 30000),
(8, 1, 'Arjun', 'Tester', 'Angular', 19000),
(9, 2, 'Nitin', 'Developer', 'MySQL', 20000),
(10, 2, 'Ramesh', 'Administrator', 'MySQL', 30000),
(11, 2, 'Rohan', 'Admin', NULL, 20000),
(12, 2, 'Raj', 'Designer', NULL, 30000);

Let us retrieve the records from the worker’s table by using the select query statement.

Code:

SELECT * FROM workers;

Output:

Now, suppose that we want to retrieve all the technologies that the workers use at the table. We will use the column technology to get the list of all the technologies. But it can be seen from the above output that there are many duplicate records for each of the technology value. Hence, we will have to use the DISTINCT() function to get the list of unique technologies that are stored in the workers’ table.

Code:

SELECT DISTINCT(technology) AS 'List Of technologies' FROM workers;

Output:

Now, let us calculate the total number of the workers with technology that are present in the table using the COUNT() function in the following query statement.

Code:

SELECT COUNT(*) as technology FROM workers;

Output:

Hence, we can see that there are 12 workers with technology in total. This count will contain all the records with duplicate technology values in it. Now, if we want to get the number of technologies with distinct values in it being considered, then we can make the use of COUNT and DISTINCT functions togetherly, as shown in the below query statement.

Code:

SELECT COUNT(DISTINCT(technology)) FROM workers;

Output:

Note that in this count, the NULL value has not been considered in the technology column. Hence, the count that is retrieved is 5.

Let us change the values of the NULL technology column and then find out the distinct count of technologies again.

We will use the following update query to replace the NULL value in technology to SQL.

Code:

UPDATE workers SET technology = 'SQL' WHERE technology IS NULL;

Let us retrieve and check the records of the table.

Code:

SELECT * FROM workers;

Output:

Now, we will again find out the count of distinct technologies and not SQL will also be considered in that count.

Code:

SELECT COUNT(DISTINCT(technology)) FROM workers ;

Output:

Now, suppose that we have to find out the workers’ table’s unique positions, then we will use the following query statement.

Code:

SELECT DISTINCT(position) AS 'List Of positions' FROM workers;

Output:

Let us now retrieve the count of distinct technologies in the workers’ table using the following query statement.

Code:

SELECT COUNT(DISTINCT(position)) FROM workers ;

Output:

Conclusion

We can make the use of the COUNT function to get the number of records from the particular table. We can add the DISTINCT keyword inside the COUNT function that will lead to consideration of only unique records while getting the count of records in SQL. DISTINCT keyword and function help us to retrieve the unique records in SQL.

Recommended Articles

This is a guide to SQL COUNT DISTINCT. Here we discuss the introduction to SQL COUNT DISTINCT along with example respectively. You may also have a look at the following articles to learn more –

1. SQL Clone Table
2. SQL ORDER BY CASE
3. SQL ORDER BY DATE
4. SQL Temporary Table