COUNT Function in SQL

The COUNT is an aggregate function in SQL which returns the total number of rows from the table.

Syntax of COUNT Function

In the Structured Query Language, we use the COUNT function with the columns of the table as shown in the following block:

In this syntax, we have to define the name and column of that table on which we want to perform the COUNT function.

Examples of COUNT function

Here, we will create the new table through which we will perform the COUNT function with the columns of the table:

The following shows the syntax to create the new table in SQL:

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

The following SELECT statement displays the inserted records of the above Product_Details table:

Query 1: The following SELECT query uses the COUNT function with the Product_Quantity column of the above Product_Details table:

Output:

Query 2: The following SELECT query uses the COUNT function with the Selling_Price column of the above Product_Details table:

This query shows the five as result because this column contains two NULL values.

Output:

Query 3: The following SELECT query uses the COUNT function with the Product_Rating column of the above Product_Details table:

This query shows the four as result because this column contains three NULL values.

Output:

Count Function with WHERE clause

We can also use the WHERE clause with the COUNT function which returns the number of rows according to the filtered rows.

The syntax for using the COUNT function with the WHERE clause is as follows:

Query 1: The following SELECT query uses the COUNT function with WHERE clause in the above Product_Details table:

This query counts those products from above table whose product id is greater than 200.

Output:

Query 2: The following SELECT query uses the COUNT function with WHERE clause in the above Product_Details table:

This query counts those products which are release on 2022-01-28.

Output:

Count Function with DISTINCT clause

We can also use the DISTINCT clause with the COUNT function which counts only distinct values of the column from the table.

The syntax for using the COUNT function with the DISTINCT clause is as follows:

Query 1: The following SELECT query uses the COUNT function with the DISTINCT clause in the above Product_Details table:

This query counts the product according to the distinct values of product_quantity column.

Output:

Query 3: The following SELECT query uses the COUNT function with the DISTINCT clause of the above Product_Details table:

This query shows the three as result because this column contains three NULL values and two same values.

Output: