The COUNT() function returns the number of rows that matches a specified criterion.
ExampleFind the total number of rows in the Products table:
SELECT COUNT(*)FROM Products; Try it Yourself » SyntaxSELECT COUNT(column_name) FROM table_name WHERE condition;
Demo DatabaseBelow is a selection from the Products table used 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 ColumnYou can specify a column name instead of the asterix symbol (*).
If you specify a column name instead of (*), NULL values will not be counted.
ExampleFind the number of products where the ProductName is not null:
SELECT COUNT(ProductName)FROM Products; Try it Yourself » Add a WHERE ClauseYou can add a WHERE clause to specify conditions:
ExampleFind the number of products where Price is higher than 20:
SELECT COUNT(ProductID)FROM ProductsWHERE Price > 20; Try it Yourself » Ignore DuplicatesYou 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.
ExampleHow many different prices are there in the Products table:
SELECT COUNT(DISTINCT Price)FROM Products; Try it Yourself » Use an AliasGive the counted column a name by using the AS keyword.
ExampleName the column "Number of records":
SELECT COUNT(*) AS [Number of records]FROM Products; Try it Yourself » Use COUNT() with GROUP BYHere we use the COUNT() function and the GROUP BY clause, to return the number of records for each category in the Products table:
Example SELECT COUNT(*) AS [Number of records], CategoryIDFROM ProductsGROUP BY CategoryID; Try it Yourself »You will learn more about the GROUP BY clause later in this tutorial.
❮ Previous Next ❯ ★ +1 Track your progress - it's free! Log in Sign Up