SQL COUNT(): How to Count Rows the Right Way (With Examples)

• 30 June 2026
• by: Renata Cardoso
• in: MySQL/MariaDB
• Tags: count function, SQL, sql count
• note: no comments

If you have ever asked your database “how many?” — how many customers, how many orders, how many sign-ups today — you have reached for SQL COUNT(). It is one of the first aggregate functions every developer learns, and one of the easiest to use slightly wrong. The function looks simple, and most of the time it is. But there is a subtle difference between `COUNT(*)` and `COUNT(column)` that quietly produces wrong numbers in real systems every single day.

In this tutorial I will walk you through every form of `COUNT()` with runnable code and real output, so you always know exactly what you are counting. We will start gentle and build up to the genuinely powerful patterns — counting per group, and filtering groups by their counts.

Key Takeaways
• `COUNT()` is an aggregate function that returns how many rows match.
• `COUNT(*)` counts every row, including rows full of NULLs.
• `COUNT(column)` counts only rows where that column is NOT NULL — this is the #1 source of “wrong” counts.
• `COUNT(DISTINCT column)` counts unique non-null values.
• Combine `COUNT()` with `GROUP BY` to count per group, and `HAVING` to filter those groups.
• Use an alias like `COUNT(*) AS total` to give the result column a readable name.

What does SQL COUNT() actually do?

`COUNT()` is an aggregate function: it takes many rows as input and collapses them into a single number. That number is how many rows matched whatever conditions your query specified.

Imagine a `customers` table:

“`sql SELECT * FROM customers; “`

“`text +—-+———-+——————+————–+

| id | name | email | phone_number | +—-+———-+——————+————–+

| 1 | Amara | [email protected] | 555-0101 |

| 2 | Ben | [email protected] | NULL |

| 3 | Chen | [email protected] | 555-0103 |

| 4 | Daniela | [email protected] | NULL |

| 5 | Esi | [email protected] | 555-0105 | +—-+———-+——————+————–+ “`

To count how many customers exist:

“`sql SELECT COUNT(*) FROM customers; “`

“`text +———-+

| COUNT(*) | +———-+

| 5 | +———-+ “`

Five rows, five customers. That is the most common thing you will ever do with `COUNT()`.

What is the difference between COUNT(\*) and COUNT(column)?

This is the part everyone gets wrong eventually, so let us be very precise.

`COUNT(*)` counts every row, period. It does not care what is in those rows. Even a row where every column is NULL still gets counted, because the row exists.

`COUNT(column)` counts only the rows where that specific column is NOT NULL. NULL values are skipped entirely.

Look what happens when we count the `phone_number` column on the same table:

“`sql SELECT COUNT(*) AS all_rows, COUNT(phone_number) AS with_phone FROM customers; “`

“`text +———-+————+

| all_rows | with_phone | +———-+————+

| 5 | 3 | +———-+————+ “`

Same table, two different numbers. `COUNT(*)` returns 5 because there are 5 rows. `COUNT(phone_number)` returns 3 because Ben and Daniela have NULL phone numbers, so they are not counted.

Here is the trap that confuses almost everyone. People reach for `COUNT(some_column)` thinking they are counting records — but they are actually counting non-null values in that column, which is a different question. The two only give the same answer when the column has no NULLs. So if you write `COUNT(phone_number)` on a table where half your users left the phone field blank, you get *half the row count*, not the user count — and you silently undercount. The rule is simple once you see it: use `COUNT(*)` when you want “how many rows” (almost always your real intent), use `COUNT(column)` only when you specifically mean “how many rows have a value in this column,” and use `COUNT(DISTINCT column)` when you want unique values. When a count comes back smaller than you expected, the very first thing to check is whether you counted a *column that contains NULLs* instead of counting `*`. That is the usual cause.

A quick reference table

Here is every form of `COUNT()` and exactly what it counts:

Form	What it counts
`COUNT(*)`	Every row, including rows with NULLs
`COUNT(column)`	Only rows where that column is NOT NULL
`COUNT(DISTINCT column)`	Unique, non-null values in that column
`COUNT(1)`	Same as `COUNT(*)` — every row (the `1` is a constant)
`COUNT(expression)`	Rows where the expression is NOT NULL

How do I count unique values with COUNT(DISTINCT)?

Sometimes you do not want “how many rows” — you want “how many *different* values.” That is what `COUNT(DISTINCT column)` is for.

Suppose we have an `orders` table:

“`sql SELECT * FROM orders; “`

“`text +———-+————-+——–+

| order_id | customer_id | amount | +———-+————-+——–+

| 101 | 1 | 40.00 |

| 102 | 1 | 15.00 |

| 103 | 3 | 60.00 |

| 104 | 5 | 20.00 |

| 105 | 5 | 35.00 |

| 106 | 5 | 10.00 | +———-+————-+——–+ “`

There are 6 orders, but how many *different customers* placed them?

“`sql SELECT COUNT(*) AS total_orders, COUNT(DISTINCT customer_id) AS unique_customers FROM orders; “`

“`text +————–+——————+

| total_orders | unique_customers | +————–+——————+

| 6 | 3 | +————–+——————+ “`

Six orders, but only three distinct customers (IDs 1, 3, and 5). `COUNT(DISTINCT customer_id)` removed the duplicates before counting.

How do I count only the rows that match a condition?

Add a `WHERE` clause. The `WHERE` filters rows *before* `COUNT()` runs, so you only count the rows that survive the filter.

Count how many orders are worth more than 30:

“`sql SELECT COUNT(*) AS large_orders FROM orders WHERE amount > 30; “`

“`text +————–+

| large_orders | +————–+

| 3 | +————–+ “`

Three orders (101 at 40, 103 at 60, and 105 at 35) cleared the 30 threshold. If you need a refresher on filtering rows, the guide covers conditions in depth.

How do I count per group with GROUP BY?

This is where `COUNT()` becomes genuinely powerful. So far we have counted the *whole* table. With `GROUP BY`, we count within each group — one count per group, all in a single query.

The classic example: how many orders did each customer place?

“`sql SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id; “`

“`text +————-+————-+

| customer_id | order_count | +————-+————-+

| 1 | 2 |

| 3 | 1 |

| 5 | 3 | +————-+————-+ “`

One row per customer, each with its own count. Customer 5 placed three orders, customer 1 placed two, customer 3 placed one. `GROUP BY customer_id` told the database to bucket the rows by customer, and `COUNT(*)` counted the rows inside each bucket. The tutorial goes deeper on grouping rules.

How do I filter groups by their count using HAVING?

You cannot use `WHERE` to filter on `COUNT()`, because `WHERE` runs *before* the rows are grouped and counted. To filter on an aggregate result, you need `HAVING`, which runs *after* grouping.

Say you want only the customers who placed more than 2 orders:

“`sql SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 2; “`

“`text +————-+————-+

| customer_id | order_count | +————-+————-+

| 5 | 3 | +————-+————-+ “`

Only customer 5 survived, because they are the only one with more than two orders. This is the standard pattern for questions like “show me the power users,” “find duplicate emails,” or “which products sold more than 100 units.”

Think of the order of operations like this:

Clause	When it runs	What it filters on
`WHERE`	Before grouping	Individual rows
`GROUP BY`	After WHERE	Buckets rows into groups
`HAVING`	After grouping	Whole groups, using aggregates like `COUNT()`

What is the difference between COUNT and SUM?

These two get mixed up, so let us settle it clearly.

• `COUNT()` answers “how many rows?” It counts records.
• `SUM()` answers “what is the total of these values?” It adds numbers together.

Same table, very different questions:

“`sql SELECT COUNT(*) AS number_of_orders, SUM(amount) AS total_revenue FROM orders; “`

“`text +——————+—————+

| number_of_orders | total_revenue | +——————+—————+

| 6 | 180.00 | +——————+—————+ “`

There are 6 orders (`COUNT`), and they add up to 180.00 in revenue (`SUM`). If you accidentally use `SUM(amount)` when you meant to count orders, you get the revenue instead of the order count — a classic reporting bug.

Why should I alias COUNT() results?

By default the result column is named after the expression, like `COUNT(*)`, which is awkward to read and even harder to reference in application code. Give it a clean name with `AS`:

“`sql SELECT COUNT(*) AS total FROM customers; “`

“`text +——-+

| total | +——-+

| 5 | +——-+ “`

Now your application can read the `total` column by a sensible name. This is a small habit that makes your far more readable, especially once you start aliasing several aggregates in one statement.

Putting it all together

Let us answer a realistic business question in one query: *”Which customers placed more than one order, and how many?”* — sorted by the busiest first.

“`sql SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 ORDER BY order_count DESC; “`

“`text +————-+————-+

| customer_id | order_count | +————-+————-+

| 5 | 3 |

| 1 | 2 | +————-+————-+ “`

That single statement filters, groups, counts, filters the groups, and sorts — a complete answer to a real question. Once `COUNT()`, `GROUP BY`, and `HAVING` click together, an enormous range of reporting questions becomes a handful of lines of SQL. To pick which columns appear in results like these, the guide is a good next read.

For the bigger picture of how databases power your website — from setup to performance — see our complete guide: MySQL & MariaDB Databases: The Complete Guide for Website Owners.

Frequently asked questions

Does COUNT(\*) count NULL values? Yes. `COUNT(*)` counts every row regardless of its contents, so a row with NULL in every column is still counted. Only `COUNT(column)` skips rows where that specific column is NULL.

Is COUNT(1) faster than COUNT(\*)? No. In modern MySQL and MariaDB, `COUNT(1)` and `COUNT(*)` are treated identically by the query optimizer and perform the same. Use `COUNT(*)` because it is the clearest, most standard way to say “count all rows.”

Why is my COUNT smaller than the number of rows? Almost always because you wrote `COUNT(column)` on a column that contains NULL values. `COUNT(column)` ignores NULLs, so it returns fewer than the total row count. Switch to `COUNT(*)` if you want every row.

Can I use COUNT in a WHERE clause? No. `WHERE` runs before rows are grouped and counted, so it cannot see an aggregate result. To filter on a count, use `HAVING` after a `GROUP BY`.

What is the difference between COUNT(DISTINCT col) and COUNT(col)? `COUNT(col)` counts all non-null values, including duplicates. `COUNT(DISTINCT col)` counts only *unique* non-null values, removing duplicates first.