The SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criterion.

Example

Find the total number of products in the Products table:

 SELECT  COUNT (*)
 FROM  products ;<

You can click on above box to edit the code and run again.

Output

Syntax

 SELECT COUNT(column_name)
 FROM  table_name
              
 WHERE  condition;

Demo Products table

This product table is used for examples:

Add a Where Clause

You can add a WHERE clause to specify conditions:

Example

Find the number of products where Price is higher than 15000:

SELECT COUNT(Product_id)
 FROM  products
WHERE Price >  15000;

You can click on above box to edit the code and run again.

Output

Specify Column

You can specify a column name instead of the asterix symbol (*) .

If you specify a column instead of (*) , NULL values will not be counted.

Example

Find the number of products where the ProductName is not null:
SELECT COUNT(Product_name)
FROM  products;

You can click on above box to edit the code and run again.

Output

Ignore Duplicates

You can ignore duplicates by using the DISTINCT keyword in the COUNT function.

If DISTINCT is specified, rows with the same value for the specified column will be counted as one.

Example

How many different prices are there in the Products table:
SELECT COUNT(DISTINCT Price)
FROM  products;

You can click on above box to edit the code and run again.

Output

Use an Alias

Give the counted column a name by using the AS keyword.

Example

Name the column "number of records":

SELECT COUNT (*)
 AS [number of records]
FROM  products;

You can click on above box to edit the code and run again.