Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

SQL COUNT

The SQL COUNT() Function

The COUNT() function yields the count of rows that meet a specified criterion.

Example

Retrieve the total count of rows in the Products table.

SELECT COUNT(*)
FROM Products;

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition

Demo Database

Here is an excerpt from the Products table utilized in the examples:

ProductID

ProductName

SupplierID

CategoryID

Unit

Price

1

Chais

1

1

10 boxes x 20 bags

18

2

Chang

1

1

24 – 12 oz bottles

19

3

Aniseed Syrup

1

2

12 – 550 ml bottles

10

4

Chef Anton’s Cajun Seasoning

2

2

48 – 6 oz jars

22

5

Chef Anton’s Gumbo Mix

2

2

36 boxes

21.35

Specify Column

Substituting a column name in place of the asterisk symbol (*) is possible.

However, when you specify a column name instead of (*), NULL values will not be included in the count.

Example

Retrieve the count of products where the ProductName is not null.

SELECT COUNT(ProductName)
FROM Products;

Add a WHERE Clause

You can incorporate a WHERE clause to define conditions.

Example

Retrieve the count of products where the Price is greater than 20.

SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;

Ignore Duplicates

By utilizing the DISTINCT keyword in the COUNT() function, duplicates can be disregarded.

When DISTINCT is employed, rows sharing the same value for the designated column will be counted as a single entity.

Example

How many distinct prices are present in the Products table:

SELECT COUNT(DISTINCT Price)
FROM Products;

Use an Alias

Assign a name to the counted column using the AS keyword.

Example

Name the column as “Number of records”.

SELECT COUNT(*) AS [Number of records]
FROM Products;

Use COUNT() with GROUP BY

In this instance, we employ the COUNT() function alongside the GROUP BY clause to retrieve the number of records for each category listed in the Products table:

Example

SELECT COUNT(*) AS [Number of records], CategoryID
FROM Products
GROUP BY CategoryID;