How to Count Unique Values in Sql

Introduction to SQL COUNT Singled-out

The following article provides an outline for SQL COUNT DISTINCT. Nosotros tin 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 back-up which means repetition of the same values in the particular result gear up. To retrieve the unique values from the result set of the particular query statement's output, we can make the utilize of distinct functions in SQL. We can employ 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 tin prove helpful in many situations in real-time example scenarios, such as retrieving the count of the grouped values having singled-out records in them.

Syntax:

COUNT ([Distinct] expression_or_value);

The expression_or_value tin can exist whatever of the column names of expression involving other aggregated functions, so that will lead to retrieval of many values that might have indistinguishable values in them.

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

Instance of SQL COUNT Distinct

Consider one instance; we will create one tabular array named workers with the aid of the following CREATE TABLE statement in SQL.

Lawmaking:

CREATE Tabular array `workers` ( `developer_id` INT(11) Not Aught, `team_id` INT(eleven) NOT Nil, `proper noun` VARCHAR(100) DEFAULT NULL, `position` VARCHAR(100) DEFAULT NULL, `engineering science` VARCHAR(100) DEFAULT Zippo, `bacon` INT(11) DEFAULT NULL )

Permit u.s. insert some records in the tabular array workers with the help of the following INSERT query statement.

Lawmaking:

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

Let u.s.a. retrieve the records from the worker's table by using the select query statement.

Lawmaking:

SELECT * FROM workers;

Output:

At present, suppose that we desire to retrieve all the technologies that the workers utilize at the table. We will use the cavalcade applied science to become the list of all the technologies. But it can exist seen from the above output that there are many duplicate records for each of the technology value. Hence, nosotros will take to use the DISTINCT() part to go the listing of unique technologies that are stored in the workers' table.

Lawmaking:

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

Output:

Now, allow us summate the total number of the workers with applied science 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 tin see that there are 12 workers with technology in total. This count volition 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, and then nosotros tin brand the use of COUNT and Distinct functions togetherly, as shown in the beneath query statement.

Lawmaking:

SELECT COUNT(Singled-out(technology)) FROM workers;

Output:

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

Let us alter the values of the NULL technology column and then find out the distinct count of technologies once more.

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

Code:

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

Permit usa retrieve and check the records of the table.

Lawmaking:

SELECT * FROM workers;

Output:

At present, we will again find out the count of distinct technologies and non SQL will also be considered in that count.

Code:

SELECT COUNT(DISTINCT(technology)) FROM workers ;

Output:

Now, suppose that we take to find out the workers' table'due south unique positions, then we will use the following query statement.

Code:

SELECT Singled-out(position) Equally 'List Of positions' FROM workers;

Output:

Let us now call back the count of distinct technologies in the workers' table using the following query statement.

Lawmaking:

SELECT COUNT(Singled-out(position)) FROM workers ;

Output:

Conclusion

We can make the utilize of the COUNT function to go the number of records from the particular table. We can add the DISTINCT keyword within the COUNT function that will pb to consideration of only unique records while getting the count of records in SQL. Distinct keyword and function help usa to recall the unique records in SQL.