MySQL COUNT() Function

The COUNT() function counts rows in a table or non-null values in a specific column. It is one of the most frequently used aggregate functions — you will reach for it any time you need to know how many: how many orders were placed, how many users registered, how many products are in a category.

---

Three Forms of COUNT()

Form	What it counts
COUNT(*)	All rows, including rows with NULL values
COUNT(column)	Only rows where the specified column is not NULL
COUNT(DISTINCT column)	Only unique, non-null values in the specified column

---

Sample Table

id	name	category	price	stock
1	Laptop	Electronics	55000.00	12
2	Phone	Electronics	25000.00	30
3	T-Shirt	Clothing	800.00	100
4	Headphones	Electronics	3500.00	NULL
5	Jeans	Clothing	2200.00	60
6	Tablet	Electronics	35000.00	18
7	Jacket	Clothing	4500.00	25

---

COUNT(*) — Count All Rows

Returns the total number of rows in the table, regardless of any NULL values:

SELECT COUNT(*) AS total_products FROM products;
-- Returns: 7

---

COUNT(column) — Count Non-NULL Values

Returns only the number of rows where the specified column has a non-null value. Notice that Headphones has NULL for stock:

SELECT COUNT(stock) AS products_with_stock FROM products;
-- Returns: 6 (Headphones row is skipped because stock is NULL)

---

COUNT(DISTINCT column)

Count how many unique values exist in a column. This is useful for questions like "how many different categories do we have?"

SELECT COUNT(DISTINCT category) AS unique_categories FROM products;
-- Returns: 2

---

COUNT() with WHERE

Filter rows before counting using WHERE:

-- How many Electronics products do we have?
SELECT COUNT(*) AS electronics_count
FROM products
WHERE category = 'Electronics';
-- Returns: 4

-- How many products cost more than 10000?
SELECT COUNT(*) AS expensive_count
FROM products
WHERE price > 10000;
-- Returns: 3

---

COUNT() with GROUP BY

Count rows per group by combining COUNT() with GROUP BY. This is one of the most common patterns in data reporting:

-- How many products are in each category?
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category;

category	total
Electronics	4
Clothing	3

---

Key Points to Remember

• COUNT(*) counts every row, including those with NULL values.
• COUNT(column) skips rows where that column is NULL.
• COUNT(DISTINCT column) counts only unique non-null values.
• Use WHERE to filter rows before counting.
• Combine with GROUP BY to get a count per group — the most common reporting pattern.
• Always use an alias (AS) to give the count result a meaningful name.