SQL COUNT DISTINCT

Before understanding the concept of Count Function with the DISTINCT keyword, we have to know about the Count and Distinct keywords. So, let's start with the Count function.

What is Count in SQL?

The COUNT is a function in Structured Query Language that shows the number of records from the table in the result. In SQL, it is always used in the SELECT query.

The syntax of the Count function is given below:

SELECT COUNT(Name_of_Column) FROM Name_of_Table;

In the count example, we have to define the name of the column in parentheses just after the COUNT keyword.

Example of Count Function

Firstly, we have to create a new table on which the count function is to be executed.

The following query creates the Teacher_Details table with Teacher_ID as the primary key using the CREATE TABLE statement:

CREATE TABLE Teacher_Details
(
Teacher_ID INT NOT NULL,
Teacher_Name varchar(100),
Teacher_Qualification varchar(50),
Teacher_Age INT,
Teacher_Interview_Marks INT
);

The following SQL queries insert the record of new teachers into the above table using INSERT INTO statement:

INSERT INTO Teacher_Details VALUES (101, Anuj, B.tech, 20, 88);
INSERT INTO Teacher_Details VALUES (102, Raman, MCA, 24, NULL);
INSERT INTO Teacher_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO Teacher_Details VALUES (107, Vikash, B.tech, 20, NULL);
INSERT INTO Teacher_Details VALUES (111, Monu, MBA, 21, NULL);
INSERT INTO Teacher_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO Teacher_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO Teacher_Details VALUES (123, Divya, B.tech, 21, NULL);
INSERT INTO Teacher_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO Teacher_Details VALUES (130, Nidhi, BBA, 20, 88);
INSERT INTO Teacher_Details VALUES (132, Priya, MBA, 22, NULL);
INSERT INTO Teacher_Details VALUES (138, Mohit, MCA, 21, 92);

Let's see the record of the above table using the following SELECT statement:

SELECT * FROM Teacher_Details;

Teacher_ID	Teacher_Name	Teacher_Qualification	Teacher_Age	Teacher_Interview_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	NULL
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	NULL
111	Monu	MBA	21	NULL
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	NULL
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	NULL
138	Mohit	MCA	21	92

The following query counts the total values of the Teacher_Age column from the Teacher_Details table:

SELECT COUNT (Teacher_Age) AS Total_Teachers_Age_Column FROM Teacher_Details;

Output:

SQL COUNT DISTINCT

The output of the above SELECT query is twelve because the Teacher_Age field does not hold any NULL value.

The following query counts the total values of Teacher_Interview_Column from the above table:

SELECT COUNT (Teacher_Interview_Marks) AS Total_Teachers_Interview_Marks FROM Teacher_Details;

This query will show the below output on the screen:

SQL COUNT DISTINCT

The output of the above SELECT query is 7 because two five cells of the Teacher_Interview_Marks column contain NULL. And these five NULL values are excluded. That's why the SELECT query displays 7 instead of 12 in the result.

What is Count(*) Function?

This is also similar to the Count function, but the only difference is that it also displays the number of NULL values from the table.

The syntax of the Count (*) Function is given here:

SELECT COUNT(*) FROM Name_of_Table;

Example:

Let's take the above Teacher_Details:

Teacher_ID	Teacher_Name	Teacher_Qualification	Teacher_Age	Teacher_Interview_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	NULL
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	NULL
111	Monu	MBA	21	NULL
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	NULL
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	NULL
138	Mohit	MCA	21	92

The following query counts the total values of the Total_Interview_Marks column from the above table:

SELECT COUNT (*) FROM Teacher_Details;

The above SELECT with COUNT(*) query will give the below result on the screen:

SQL COUNT DISTINCT

What is DISTINCT in SQL?

The DISTINCT keyword shows the unique rows of the column from the table in the result.

The syntax of the DISTINCT keyword is given here:

SELECT DISTINCT Name_of_Column FROM Table_Name WHERE [condition];

In the DISTINCT query, we can also define the condition in the WHERE clause for retrieving the specific values.

Example of DISTINCT

First, create a new table on which the Distinct keyword is to be run.

The following query creates the Bike_Details table using the CREATE TABLE statement:

CREATE TABLE Bike_Details
(
Bike_Name varchar(100),
Bike_Model INT,
Bike_Color varchar(50),
Bike_Cost INT );

The following SQL queries insert the record of new bikes into the table using the INSERT INTO statement:

INSERT INTO Bike_Details VALUES (KTM DUKE, 2019, Black, 185000);
INSERT INTO Bike_Details VALUES (royal Infield, 2020, Black, 165000);
INSERT INTO Bike_Details VALUES (Pulsar, 2018, Red, 90000);
INSERT INTO Bike_Details VALUES (Apache, 2020, White, 85000);
INSERT INTO Bike_Details VALUES (Livo, 2018, Black, 80000);
INSERT INTO Bike_Details VALUES (KTM RC, 2020, Red, 195000);

The records of the above table are shown by using the following SELECT query:

SELECT * FROM Bike_Details;

Bike_Name	Bike_Model	Bike_Color	Bike_Cost
KTM DUKE	2019	Black	185000
Royal Enfield	2020	Black	165000
Pulsar	2018	Red	90000
Apache	2020	White	85,000
Livo	2018	Black	80,000
KTM RC	2020	Red	195,000

Table: Bike_Details

The following SQL query the distinct values of the Color column from the above Bike_Details table:

SELECT DISTINCT Bike_Color FROM Bikes ;

Output:

SQL COUNT DISTINCT

As we can see, Black, Red, and White are three distinct values in the Bike_Color column.

Count Function with DISTINCT keyword

The DISTINCT keyword with the COUNT function in the SELECT query displays the number of unique data of the field from the table.

The Syntax of Count Function With DISTINCT keyword is given below:

SELECT COUNT(DISTINCT (Column_Name) FROM table_name WHERE [condition];

Examples of Count Function with DISTINCT keyword

The following two SQL examples will explain the execution of Count Function with Distinct keyword:

Example 1:

The following query creates the College_Students table with four fields:

CREATE TABLE College_Students
(
Student_Id INT NOT NULL,
Student_Name Varchar (40),
Student_Age INT,
Student_Marks INT
);

The following INSERT query inserts the record of students into the College_Students table:

INSERT INTO College_Students (Student_Id, Student_Name, Student_Age, Student_Marks) VALUES (101, Akhil, 28, 95),
(102, Abhay, 27, 86),
(103, Sorya, 26, 79),
(104, Abhishek, 27, 66),
(105, Ritik, 26, 79),
(106, Yash, 29, 88);

The following query shows the details of the College_Students table:

SELECT * FROM College_Students;

Student_Id	Student_Name	Student_Age	Student_Marks
101	Akhil	28	95
102	Abhay	27	86
103	Sorya	26	79
104	Abhishek	27	66
105	Ritik	26	79
106	Yash	29	88

Table: College_Students

The following SQL statement counts the unique values of the Student_Age column from the College_Students table:

SELECT COUNT (DISTINCT (Student_Age) AS Unique_Age FROM College_Students ;

This query will give the below table in the output:

SQL COUNT DISTINCT

The output shows the four values because the Teacher_age column contains 4 unique values.

Example 2:

The following query creates the IT_Employee table with four fields:

CREATE TABLE IT_Employee
(
Employee_Id INT NOT NULL,
Employee_Name Varchar (40),
Emp_Age INT,
Employee_Salary INT
);

The following INSERT query inserts the record of IT employees into the IT_Employee table:

INSERT INTO IT_Employee (Employee_Id, Employee_Name, Employee_Age, Employee_Salary) VALUES (101, Akhil, 28, 25000),
(102, Abhay, 27, 26000),
(103, Sorya, 26, 29000),
(104, Abhishek, 27, 26000),
(105, Ritik, 26, 29000),
(106, Yash, 29, 25000);

The following query shows the details of the IT_Employee table:

SELECT * FROM IT_Employee;

Employee_Id	Employee_Name	Employee_Age	Employee_Salary
101	Akhil	28	25000
102	Abhay	27	26000
103	Sorya	26	29000
104	Abhishek	27	26000
105	Ritik	26	29000
106	Yash	29	25000

Table: IT_Employee

The following SQL statement counts only the unique values of the Emp_Age column from the above IT_Employee table:

SELECT COUNT (DISTINCT (Employee_Age)) AS Unique_Age FROM IT_Employee ;

This query will give the below output:

SQL COUNT DISTINCT

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz