# 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:

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:

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

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

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:

Output:

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:

This query will show the below output on the screen:

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:

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:

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

### 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:

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:

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

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

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:

Output:

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:

### 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:

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

The following query shows the details of the College_Students table:

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:

This query will give the below table in the output:

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:

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

The following query shows the details of the IT_Employee table:

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:

This query will give the below output:

Next TopicSQL UNION