SQL COUNT() Function
The COUNT() function in SQL counts rows in a table or non-null values in a column. It's a fundamental aggregate function used for summarizing data.
COUNT(): Definition and Usage
COUNT() is extremely useful for getting totals and understanding the size of your datasets. It's often used with GROUP BY to count items within different categories. The key thing to remember is that COUNT(column_name) ignores NULL values; it only counts rows where the specified column actually contains a value. COUNT(*), however, counts *all* rows in the table, including those with NULL values in the specified column.
Syntax
Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Example Database: Products Table
The examples below use this sample 'Products' table:
| 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 |
Examples
Counting All Rows
This counts all rows in the 'Products' table, regardless of NULL values.
Syntax
SELECT COUNT(*) FROM Products;
Output
5
Counting Non-Null Product Names
This counts only rows where 'ProductName' is not NULL.
Syntax
SELECT COUNT(ProductName) FROM Products;
Output
5
Counting with a WHERE Clause
This counts products where the price is over 20.
Syntax
SELECT COUNT(ProductID) FROM Products WHERE Price > 20;
Output
2
Counting Distinct Values
This counts the number of *unique* prices.
Syntax
SELECT COUNT(DISTINCT Price) FROM Products;
Output
5
Using an Alias
This names the output column "Number of records".
Syntax
SELECT COUNT(*) AS "Number of records" FROM Products;
Output
Number of records
-----------------
5
Using COUNT() with GROUP BY
This counts products per category.
Syntax
SELECT COUNT(*) AS "Number of records", CategoryID
FROM Products
GROUP BY CategoryID;
Output
Number of records | CategoryID
-----------------|------------
2 | 1
3 | 2