The COUNT() function in MySQL is an aggregate function used to count rows or values in a result set. It helps analyze how many records meet specific conditions in a query.
- Counts total rows or non-NULL values based on the column or condition used.
- Can be applied to entire tables, specific columns, or filtered results using WHERE.
- Commonly used with GROUP BY to count records within each group.
- Accepts a single expression and ignores NULL values while counting.
Syntax:
COUNT(expression)expression: This can be a column name, *, or an expression
Working with the COUNT() Function
This section shows how COUNT() works using a sample table. It demonstrates counting total, non-NULL, and unique values. First, we will create a demo table on which the COUNT() function will be applied:
Example 1: COUNT(*) Function
This example counts the total number of rows in the sales table. It includes all rows regardless of NULL values.
Query:
SELECT COUNT(*) AS TotalRows
FROM sales;Output:
- Counts all rows in the table without considering NULL values.
- Returns the total number of records present in the table.
Example 2: COUNT(expression) Function
This example counts the number of rows where the quantity column is not NULL. Only valid values are considered.
Query:
SELECT COUNT(quantity) AS NonNullQuantities
FROM sales;Output:
- Counts only non-NULL values in the quantity column.
- Ignores rows where quantity contains NULL.
Example 3: COUNT(DISTINCT expression) Function
This example counts the number of unique product names in the sales table. Duplicate values are counted only once.
Query:
SELECT COUNT(DISTINCT product_name) AS UniqueProducts
FROM sales;Output:
- Counts only distinct (unique) values in the product_name column.
- Ignores duplicate entries like repeated "Laptop" values.