SQL Count

The SQL COUNT() Function

The number of rows that satisfy a certain criterion is returned by the COUNT() method.

Find the total number of rows in the Products table:

				
					SELECT COUNT(*)
FROM Products;

				
					SELECT COUNT(column_name)
FROM table_name
WHERE condition;

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

The asterix symbol (*) can be substituted with the name of a column.

NULL values will not be tallied if a column name is entered in place of the symbol (*).

Find the number of products where the ProductName is not null:

				
					SELECT COUNT(ProductName)
FROM Products;

A WHERE clause can be added to establish conditions:

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

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

The COUNT() function’s DISTINCT keyword can be used to ignore duplicates.

Rows that have the same value in the designated column will count as one if DISTINCT is selected.

How many different prices are there in the Products table:

				
					SELECT COUNT(DISTINCT Price)
FROM Products;

Use the AS keyword to assign a name to the counted column.

Name the column “Number of records”:

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

Here, we retrieve the number of records for each category in the Products table using the GROUP BY clause and the COUNT() function:

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