SQL SELECT COUNT

The SQL COUNT() is a function that returns the number of records of the table in the output.

This function is used with the SQL SELECT statement.

Let's take a simple example: If you have a record of the voters in the selected area and want to count the number of voters, then it is very difficult to do it manually, but you can do it easily by using SQL SELECT COUNT query.

Syntax of Select Count Function in SQL

In the syntax, we have to specify the column's name after the COUNT keyword and the name of the table on which the Count function is to be executed.

Examples of Select Count Function in SQL

In this article, we have taken the following two SQL examples that will help you to run the Count function in the query:

Example 1: In this example, we have a table called Bike with three columns:

Bike_NameBike_ColorBike_Cost
PulsarBlack185,000
ApacheBlackNULL
KTM RCRed90,0000
Royal EnfieldWhiteNULL
LivoBlack80,000
KTM DUKERed195,000
  • Suppose, you want to count the total number of bike colors from Bike Table. For this operation, you have to write the following SQL statement:

This query will show the following output on the screen:

TotalBikeColor
6

The output of this query is six because the Bike_Color column does not contain any NULL value.

  • Suppose, you want to count the total values of the Bike_Cost column from the above Bike Table. For this operation, you have to write the following statement in SQL:

This query will show the following output on the screen:

TotalBikeCost
4

The output of this query is four because two values of the Bike_Cost column are NULL and, these two NULL values are excluded from the count function. That's why this query shows four instead of 6 in the output.

Example 2: In this example, we have an Employee_details table with four columns:

Emp_IdEmp_NameEmp_SalaryEmp_City
2001Saurabh25000NULL
2002Ram29000Delhi
2003Sumit30000NULL
2004Ankit45000Goa
2005Bheem40000NULL
  • Suppose, you want to count the total values of the Emp_City column of the above Employee_details table. For this query, you have to write the following statement in Structured Query Language:

This query will show the following output on the screen:

TotalCity
2

The output of this query is two because the three values of the Emp_City column are NULL. And, these three NULL values are excluded from the count function. That's why this query shows two instead of 5 in the output.

Select Count(*) Function in SQL

The count(*) function in SQL shows all the Null and Non-Null records present in the table.

Syntax of Count (*) Function in SQL

Example of Count (*) Function in SQL

In this example, we have the following Bike table with three columns:

Bike_NameBike_ColorBike_Cost
LivoBlack185,000
ApacheRedNULL
PulsarRed90,0000
Royal EnfieldBlackNULL
KTM DUKEBlack80,000
KTM RCWhite195,000
  • Suppose, you want to count the total number of records from the Bike Table. For this condition, you have to write the following statement in Structured Query Language:

This query will show the following output on the screen:

Count(*)
6

SQL Count() Function With WHERE Clause

We can also use the Count() function with the WHERE clause. The Count Function with WHERE clause in the SELECT statement shows those records that matched the specified criteria.

Syntax of Count() Function With WHERE clause in SQL

Examples of Count Function With WHERE clause in SQL

The following two examples will help you to run the Count function with the WHERE clause in the SQL query:

Example 1: In this example, we have the following Bike table with three columns:

Bike_NameBike_ColorBike_Cost
ApacheBlack90,0000
LivoBlackNULL
KTM RCRed185,000
KTM DUKEWhiteNULL
Royal EnfieldRed80,000
PulsarBlack195,000
  • Suppose, you want to count the total number of bikes whose color is black. For this, you have to type the following statement in SQL:

This query will show the following output on the screen:

TotalBikeBlackColor
3

Example 2: In this example, we have an Employee_details table with four columns:

Emp_IdEmp_NameEmp_SalaryEmp_City
2001Bheem30000Jaipur
2002Ankit45000Delhi
2003Sumit40000Delhi
2004Ram29000Goa
2005Abhay25000Delhi
  • Suppose, you want to count the total number of those employees who belong to Delhi city. For this, you have to write the following SQL statement:

This query will show the following output on the screen:

TotalEmpCity
3

SQL Count Function With DISTINCT keyword

The DISTINCT keyword with the COUNT function shows only the numbers of unique rows of a column.

Syntax of Count Function With DISTINCT keyword in SQL

Examples of Count Function With DISTINCT keyword in SQL

The following two examples will help you how to run the Count function with the DISTINCT keyword in the SQL query:

Example 1:

In this example, we have taken the following Cars table with three columns:

Car_NameCar_ColorCar_Cost
i20White10,85,000
Hyundai VenueBlack9,50,000
Swift DezireRed9,00,000
Hyundai CretaWhite7,95,000
Kia SeltosWhite8,00,000
Kia SonetRed10,00,000
  • Suppose, you want to count the unique colors of a car from the above table. For this query, you have to write the below statement in SQL:

This query will show the following output on the screen:

Unique_Car_Color
3

The output of this query is three because there are three unique values of the car.

Example 2:

In this example, we have taken an Employee table with four columns:

Emp_IdEmp_NameEmp_SalaryEmp_City
2001Sumit25000Jaipur
2002Ram45000Delhi
2003Bheem25000Delhi
2004Ankit29000Goa
2005Abhay40000Delhi
  • Suppose, you want to count the unique values of the Emp_Salaryfield from the Employee_details table. For this, you have to write the following statement in Structured Query Language:

This query will show the following output on the screen:

Unique_Salary
4

Next TopicSQL SELECT TOP