The COUNT() function yields the count of rows that meet a specified criterion.
Retrieve the total count of rows in the Products table.
SELECT COUNT(*) FROM Products; |
SELECT COUNT(column_name) |
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 |
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.
Retrieve the count of products where the ProductName is not null.
SELECT COUNT(ProductName) FROM Products; |
You can incorporate a WHERE clause to define conditions.
Retrieve the count of products where the Price is greater than 20.
SELECT COUNT(ProductID) FROM Products WHERE Price > 20; |
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.
How many distinct prices are present in the Products table:
SELECT COUNT(DISTINCT Price) FROM Products; |
Assign a name to the counted column using the AS keyword.
Name the column as “Number of records”.
SELECT COUNT(*) AS [Number of records] FROM Products; |
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:
SELECT COUNT(*) AS [Number of records], CategoryID FROM Products GROUP BY CategoryID; |