EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL COUNT DISTINCT

Home » Data Science » Data Science Tutorials » SQL Tutorial » 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.

Popular Course in this category
Sale
JDBC Training (6 Courses, 7+ Projects)6 Online Courses | 7 Hands-on Projects | 37+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,929 ratings)
Course Price

View Course

Related Courses
PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)SQL Training Program (7 Courses, 8+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

EDUCBA

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

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

Special Offer - JDBC Training Course Learn More