SQL Count

The COUNT() function is one of SQL’s core aggregate functions, and by far the most frequently used one. It answers a simple but essential question: “how many rows match this?” Whether you need the total number of rows in a table, the number of rows where a particular column actually has a value, or the number of distinct values in a column, COUNT() is the tool for the job. Getting comfortable with its three forms — and exactly how each one treats NULL — will save you from some of the most common, and hardest to spot, bugs in SQL reporting code.

Overview: How COUNT Works

COUNT() is an aggregate function: it takes many rows as input and collapses them into a single number. When used without GROUP BY, it produces exactly one row of output summarizing the entire result set. When used with GROUP BY, the database engine first partitions the rows into buckets based on the grouping column(s), and then runs COUNT() separately within each bucket, producing one count per group.

There are three forms you need to know:

COUNT(*) — counts every row in the result set, regardless of whether any column contains NULL. It does not evaluate any particular column; it simply tallies rows.
COUNT(column_name) — counts only the rows where column_name is not NULL. Any row where that column is NULL is skipped.
COUNT(DISTINCT column_name) — counts the number of unique, non-NULL values that appear in column_name. Duplicate values are only counted once, and NULLs are ignored entirely.

Under the hood, the database engine typically executes a query in this logical order: it scans the table(s) named in FROM, applies any JOINs, filters rows using WHERE, then (if present) groups the surviving rows by the GROUP BY columns. COUNT() and other aggregates are computed once per group at this point. If a HAVING clause is present, it filters out entire groups based on the aggregated values — which is why you can write HAVING COUNT(*) > 1 but not the same thing in WHERE. Finally the engine builds the output columns from SELECT, applies ORDER BY, and trims the result with LIMIT if one is given.

Syntax

SELECT COUNT(*) | COUNT(column_name) | COUNT(DISTINCT column_name)
FROM table_name
[WHERE condition]
[GROUP BY column_name]
[HAVING aggregate_condition]
[ORDER BY column_name];

Part	Meaning
`COUNT(*)`	Counts all rows, including rows that contain `NULL` values in any column.
`COUNT(column_name)`	Counts only rows where `column_name` is not `NULL`.
`COUNT(DISTINCT column_name)`	Counts the number of unique, non-`NULL` values in `column_name`.
`WHERE condition`	Filters individual rows before counting happens. Cannot reference aggregate functions.
`GROUP BY column_name`	Produces one count per distinct value (or combination of values) in the grouping column(s).
`HAVING aggregate_condition`	Filters entire groups after counting, based on the aggregated result.

Examples

Example 1: Counting all rows

SELECT COUNT(*) AS total_employees
FROM employees;

Result:

total_employees
4

This is the simplest and most common use of COUNT(): get the total number of rows in a table. Since COUNT(*) doesn’t look at any specific column’s values, it isn’t affected by NULLs — it just counts rows.

Example 2: COUNT(*) vs COUNT(column) with NULLs

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    rating INTEGER
);

INSERT INTO reviews (id, product_name, rating) VALUES
    (1, 'Widget', 5),
    (2, 'Widget', 4),
    (3, 'Gadget', NULL),
    (4, 'Gadget', 3),
    (5, 'Gizmo', NULL);

SELECT COUNT(*) AS total_rows, COUNT(rating) AS rated_rows
FROM reviews;

Result:

total_rows	rated_rows
5	3

The reviews table has 5 rows, so COUNT(*) returns 5. But two of those rows (ids 3 and 5) have a NULL rating — meaning no rating was actually submitted. COUNT(rating) skips those rows and only counts the 3 rows where a rating value is present. This distinction matters constantly in real reporting: “how many orders exist” and “how many orders have a discount code applied” are two different questions, and mixing up COUNT(*) with COUNT(some_nullable_column) is an easy way to get the wrong answer.

Example 3: Counting distinct values

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    rating INTEGER
);

INSERT INTO reviews (id, product_name, rating) VALUES
    (1, 'Widget', 5),
    (2, 'Widget', 4),
    (3, 'Gadget', NULL),
    (4, 'Gadget', 3),
    (5, 'Gizmo', NULL);

SELECT COUNT(DISTINCT product_name) AS distinct_products
FROM reviews;

Result:

distinct_products
3

Even though there are 5 rows in the table, there are only 3 unique values in product_name: Widget, Gadget, and Gizmo. COUNT(DISTINCT ...) is what you reach for when duplicates in the underlying rows would otherwise inflate a plain COUNT() — for example, counting how many different products have been reviewed, rather than how many reviews exist.

Example 4: COUNT with GROUP BY and HAVING

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    rating INTEGER
);

INSERT INTO reviews (id, product_name, rating) VALUES
    (1, 'Widget', 5),
    (2, 'Widget', 4),
    (3, 'Gadget', NULL),
    (4, 'Gadget', 3),
    (5, 'Gizmo', NULL);

SELECT product_name, COUNT(*) AS review_count
FROM reviews
GROUP BY product_name
HAVING COUNT(*) > 1
ORDER BY product_name;

Result:

product_name	review_count
Gadget	2
Widget	2

Here the engine groups the 5 rows by product_name, producing three buckets: Widget (2 rows), Gadget (2 rows), and Gizmo (1 row). COUNT(*) is computed per bucket, and then HAVING COUNT(*) > 1 discards the Gizmo bucket because it doesn’t have more than one review. ORDER BY product_name then sorts the remaining groups alphabetically.

How It Works Step by Step

Take the query from Example 4 and trace it through the engine’s logical processing order:

FROM — the engine starts by reading every row from reviews.
WHERE — there is no WHERE clause here, so no rows are filtered out yet; all 5 rows continue.
GROUP BY product_name — the 5 rows are partitioned into 3 groups: Widget, Gadget, Gizmo.
Aggregation — COUNT(*) is evaluated once per group: Widget = 2, Gadget = 2, Gizmo = 1.
HAVING COUNT(*) > 1 — groups are filtered based on their aggregated count. Gizmo (count = 1) is dropped; Widget and Gadget remain.
SELECT — the output columns product_name and review_count are produced for the surviving groups.
ORDER BY product_name — the remaining rows are sorted alphabetically, giving Gadget before Widget.

This order explains a rule that trips up a lot of beginners: you can filter on an aggregate in HAVING, but not in WHERE, because WHERE runs before grouping and aggregation even happen — at that point in the pipeline, there is no count yet to filter on.

Common Mistakes

Mistake 1: Using COUNT(*) when you meant COUNT(DISTINCT …)

It’s easy to write a query intending to count unique entities but accidentally count rows instead.

Wrong:

SELECT COUNT(*) AS unique_customers
FROM orders;

Result: unique_customers = 3. This is technically correct as “number of order rows,” but the alias is misleading — it claims to count unique customers, while COUNT(*) actually counts every order row, including repeat orders from the same customer.

Why it’s wrong: the orders table has 3 rows, but those rows only reference 2 distinct customers. A reader trusting the alias would wrongly conclude there are 3 unique customers.

Corrected:

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result: unique_customers = 2, which correctly reflects that the 3 orders were placed by only 2 distinct customers.

Mistake 2: Putting an aggregate function in WHERE instead of HAVING

Wrong:

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE COUNT(*) > 1
GROUP BY department;

Why it’s wrong: WHERE filters individual rows before grouping and aggregation take place, so there is no aggregated count available yet for it to compare against. Running this raises an error such as “misuse of aggregate function COUNT()”.

Corrected:

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Moving the condition to HAVING works because HAVING runs after grouping and aggregation, so COUNT(*) already has a value to filter on for each department group.

Best Practices

Use COUNT(*) when you want the total number of rows, regardless of which columns might be NULL — it’s also typically the most efficient form since the engine doesn’t need to inspect a specific column’s values.
Use COUNT(column_name) only when you specifically want to know how many rows have a non-NULL value in that column — don’t use it as a stand-in for total row count.
Use COUNT(DISTINCT column_name) when duplicates would otherwise overstate the answer, such as counting unique customers, products, or categories.
Always give your COUNT() expression an alias with AS so the output column is self-explanatory instead of a generic COUNT(*) label.
Filter groups with HAVING, never WHERE, when the condition depends on an aggregate result like COUNT().
Be explicit about which form of COUNT() you mean in code reviews and documentation — “count of orders” and “count of unique customers” are easy to conflate, and the SQL itself won’t warn you if you pick the wrong one.

Practice Exercises

Exercise 1: Using the employees table, write a query that returns the total number of employees, and separately, the number of distinct departments they work in.
Exercise 2: Using the products table, write a query that groups products by category and returns the number of products in each category.
Exercise 3: Using the orders table, write a query that counts how many orders each customer_id has placed, then keeps only customers with more than one order. (Hint: you’ll need GROUP BY and HAVING.)

Summary

COUNT(*) counts every row, unaffected by NULL values in any column.
COUNT(column_name) counts only rows where that specific column is not NULL.
COUNT(DISTINCT column_name) counts unique, non-NULL values, ignoring duplicates.
With GROUP BY, COUNT() is computed once per group rather than once for the whole table.
Filter on aggregated counts with HAVING, not WHERE — WHERE runs before grouping and aggregation occur.
Always alias your COUNT() output and pick the specific form (*, column, or DISTINCT) that matches what you actually intend to measure.

SQL Count

Overview: How COUNT Works

Syntax

Examples

Example 1: Counting all rows

Example 2: COUNT(*) vs COUNT(column) with NULLs

Example 3: Counting distinct values

Example 4: COUNT with GROUP BY and HAVING

How It Works Step by Step

Common Mistakes

Mistake 1: Using COUNT(*) when you meant COUNT(DISTINCT …)

Mistake 2: Putting an aggregate function in WHERE instead of HAVING

Best Practices

Practice Exercises

Summary

Table of Contents