EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL COUNT DISTINCT

SQL COUNT DISTINCT

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

SQL Count Distinct 1

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:

SQL Count Distinct 2

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:

total number of the workers

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:

SQL Count Distinct 4

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:

retrieve and check the records

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:

SQL Count Distinct 6

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:

SQL Count Distinct 7

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:

in the workers

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

We hope that this EDUCBA information on “SQL COUNT DISTINCT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Clone Table
  2. SQL ORDER BY CASE
  3. SQL ORDER BY DATE
  4. SQL Temporary Table
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more