Elevate your SQL proficiency with the COUNT() function! Learn efficient data analysis through practical examples, mastering record counting for enhanced queries.
The COUNT()
function returns the number of rows that matches a specified criterion.
Find the total number of products in the Products
table:
SELECT COUNT(*)
FROM Products;
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Below 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 |
You can add a WHERE
clause to specify conditions:
Find the number of products where Price
is higher than 20:
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;
You can specify a column name instead of the asterix symbol (*)
.
If you specify a column instead of (*)
, NULL values will not be counted.
Find the number of products where the ProductName
is not null:
SELECT COUNT(ProductName)
FROM Products;
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.
How many different prices are there in the Products
table:
SELECT COUNT(DISTINCT Price)
FROM Products;
Give the counted column a name by using the AS
keyword.
Name the column "number of records":
SELECT COUNT(*) AS [number of records]
FROM Products;
#sql