SQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) Explained
SQL COUNT looks simple, but COUNT(*), COUNT(column) and COUNT(DISTINCT column) answer three different questions. Learn exactly how each treats NULLs and duplicates, conditional counts, GROUP BY and HAVING, the LEFT JOIN trap that counts customers with zero orders as one, and what each costs, with every query run.
- SQL COUNT(*) counts rows;
COUNT(column)counts non-NULL values;COUNT(DISTINCT column)counts unique non-NULL values. - On our 8-row table they returned 8, 4 and 3 for the same coupon column.
- With a LEFT JOIN,
COUNT(*)reported 1 order for a customer with none. Count a column from the right table instead. - COUNT never returns NULL: an empty result gives 0, while SUM gives NULL.
“How many orders did we get?” “How many customers used a coupon?” “How many different cities?” All three sound like the same question, and all three use COUNT. Yet the answers can differ wildly depending on which form you write, how NULLs are stored and how tables are joined. This guide walks through every variant of SQL COUNT with a small table built to expose the differences.
All queries ran on SQLite 3.50.4 and the output is copied from the run. COUNT behaves the same way in MySQL, PostgreSQL, SQL Server and Oracle, except where we note otherwise.
What does COUNT do in SQL?
COUNT is an aggregate function: it reduces many rows to one number. With GROUP BY it returns one number per group. The argument decides what is counted:
COUNT(*) -- rows
COUNT(expression) -- rows where the expression is not NULL
COUNT(DISTINCT expr) -- distinct non-NULL values of the expression
The sample data
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, city TEXT,
coupon TEXT, amount INT);
INSERT INTO orders (customer, city, coupon, amount) VALUES
('Asha','Pune','SAVE10',900), ('Ravi','Delhi',NULL,20), ('Asha','Pune',NULL,20),
('Meera','Pune','SAVE10',900),('Ravi','Delhi','WELCOME',20),('Kabir',NULL,NULL,300),
('Lina','Goa','',40), ('Zoya','Goa',NULL,NULL);
Eight orders. Four have no coupon (NULL), one has an empty-string coupon, two share SAVE10, one city is missing, and one amount is missing.
The four counts side by side
SELECT COUNT(*) AS star,
COUNT(1) AS one,
COUNT(coupon) AS coupon_col,
COUNT(DISTINCT coupon) AS distinct_coupon,
COUNT(*) - COUNT(coupon) AS null_coupons
FROM orders;
-- star | one | coupon_col | distinct_coupon | null_coupons
-- 8 | 8 | 4 | 3 | 4
One table, four different numbers. Each one is correct for a different question, which is why the form you choose matters.
COUNT(*) and COUNT(1): counting rows
COUNT(*) counts every row the query produces, regardless of what the columns contain. The star does not mean “all columns are read”; it means “count the row itself”. COUNT(1) counts the constant 1 for each row, which is never NULL, so it always returns the same result. In most databases the optimizer treats them identically; in our SQLite timing below, COUNT(*) was actually much faster. Prefer COUNT(*): it is the standard idiom and states the intent clearly.
COUNT(column): counting non-NULL values
COUNT(coupon) returned 4 because four rows have a NULL coupon and NULLs are skipped. That makes it the right tool for “how many orders have a coupon value?” and the wrong tool for “how many orders are there?”. The difference COUNT(*) - COUNT(column) gives the number of missing values, a handy data-quality check. Our SQL NULL values guide explains why aggregates skip NULLs.
COUNT(DISTINCT): counting unique values
COUNT(DISTINCT coupon) returned 3: SAVE10, WELCOME and the empty string. The two SAVE10 orders count once, and NULL is never counted at all. To count distinct combinations of columns, count rows from a subquery:
SELECT COUNT(*) AS pairs
FROM (SELECT DISTINCT customer, city FROM orders) AS d;
-- 6
MySQL also accepts COUNT(DISTINCT customer, city), but PostgreSQL, SQLite and SQL Server do not, so the subquery is the portable form. Our SQL DISTINCT guide covers the related traps.
Empty strings are not NULL
SELECT COUNT(coupon) AS non_null,
SUM(coupon = '') AS empty_strings,
COUNT(NULLIF(coupon, '')) AS real_coupons
FROM orders;
-- non_null | empty_strings | real_coupons
-- 4 | 1 | 3
Lina’s order has an empty-string coupon, which COUNT(coupon) happily counts. If empty strings mean “no coupon” in your data, wrap the column in NULLIF(coupon, '') so they become NULL before counting. Oracle is the exception here: it stores empty strings as NULL. Pairing NULLIF with COALESCE covers most cleaning needs.
COUNT with GROUP BY
SELECT city, COUNT(*) AS orders, COUNT(DISTINCT customer) AS customers
FROM orders GROUP BY city ORDER BY orders DESC;
-- Pune | 3 | 2
-- Goa | 2 | 2
-- Delhi | 2 | 1
-- NULL | 1 | 1
Each group gets its own count. Rows with a NULL city form their own group, so they are not lost. Delhi has two orders but one customer, a difference that only shows up when you count both ways. More in our GROUP BY and HAVING guide.
Conditional counts
SELECT COUNT(*) FILTER (WHERE amount >= 100) AS big,
SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 END) AS small,
COUNT(CASE WHEN coupon IS NOT NULL THEN 1 END) AS with_coupon
FROM orders;
-- big | small | with_coupon
-- 3 | 4 | 4
Counting several conditions in one pass avoids running separate queries. The standard FILTER clause works in PostgreSQL and SQLite; the CASE forms work everywhere, including MySQL and SQL Server. COUNT(CASE ... END) works because CASE without ELSE returns NULL for non-matching rows, which COUNT skips. Note that big plus small is 7, not 8: Zoya’s NULL amount matched neither condition. See SQL CASE WHEN for more patterns.
Filtering groups with HAVING
SELECT customer, COUNT(*) AS n
FROM orders GROUP BY customer HAVING COUNT(*) > 1;
-- Asha | 2
-- Ravi | 2
WHERE filters rows before counting; HAVING filters groups after counting. “Customers with more than one order” needs HAVING, because the count does not exist until the groups are formed.
The LEFT JOIN counting trap
We added a customers table that includes Dev, who has never ordered, and counted orders per customer two ways:
SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS right
FROM customers c LEFT JOIN orders o ON o.customer = c.name
GROUP BY c.name ORDER BY c.name;
-- Asha | 2 | 2
-- Dev | 1 | 0
-- Kabir | 1 | 1
-- (Lina, Meera, Ravi and Zoya also match in both columns)
A LEFT JOIN keeps Dev with NULLs in every order column, so the joined result still has one row for Dev and COUNT(*) reports 1 order. COUNT(o.id) skips the NULL and correctly reports 0. Whenever you count matches after a LEFT JOIN, count a non-nullable column from the right-hand table, usually its primary key. Our SQL joins guide shows why outer joins produce these rows.
Tip: if a report shows a minimum of 1 for “orders per customer”, suspect COUNT(*) over a LEFT JOIN.
COUNT as a window function
SELECT customer, amount,
COUNT(*) OVER (PARTITION BY customer) AS orders_by_customer
FROM orders ORDER BY customer LIMIT 4;
-- Asha | 900 | 2
-- Asha | 20 | 2
-- Kabir | 300 | 1
-- Lina | 40 | 1
With OVER, COUNT keeps every row and attaches the group count to it, instead of collapsing the group. That is useful for showing “order 1 of 2” or filtering customers with more than N orders while keeping the order details. See SQL window functions.
COUNT on an empty result
SELECT COUNT(*) AS n, SUM(amount) AS total FROM orders WHERE amount > 5000;
-- n | total
-- 0 | NULL
COUNT always returns a number, 0 when nothing matches. SUM, AVG, MIN and MAX return NULL instead, which can break application code expecting a number. Wrap them in COALESCE(SUM(amount), 0) when you need zero.
Performance of the COUNT variants
We counted a 1,000,000-row SQLite table (best of three runs):
| Query | Result | Time |
|---|---|---|
COUNT(*) |
1,000,000 | 0.1 ms |
COUNT(1) |
1,000,000 | 13.0 ms |
COUNT(ref), 30% NULLs |
699,353 | 43.0 ms |
COUNT(DISTINCT user_id), no index |
50,000 | 240.0 ms |
COUNT(DISTINCT user_id), with index |
50,000 | 27.4 ms |
SQLite has a special fast path for a bare COUNT(*) over a whole table, so it returned almost instantly, while COUNT(1) scanned every row. Other engines differ, according to their documentation (not run here): PostgreSQL must scan for an exact count, and InnoDB in MySQL also scans, using the smallest index. Counting a column has to check each value for NULL, and DISTINCT needs sorting or hashing, which an index on the column speeds up dramatically, here almost 9 times. See our SQL indexes guide. For dashboards on huge tables, consider approximate counts or cached totals.
SQL COUNT cheat sheet
| Question | Use |
|---|---|
| How many rows? | COUNT(*) |
| How many rows have a value? | COUNT(column) |
| How many missing values? | COUNT(*) - COUNT(column) |
| How many different values? | COUNT(DISTINCT column) |
| How many matching a condition? | COUNT(*) FILTER (WHERE ...) or SUM(CASE ...) |
| Matches per parent after LEFT JOIN? | COUNT(child.id) |
| Groups with more than N rows? | HAVING COUNT(*) > N |
Final thoughts
SQL COUNT is three tools in one. COUNT(*) counts rows, COUNT(column) counts values that are present, and COUNT(DISTINCT column) counts unique values, all ignoring NULL except the first. Watch for empty strings that should be NULL, count a right-table key after LEFT JOINs, and use conditional counts to answer several questions in one pass. Pick the form that matches the question, and your numbers will be right the first time.
Frequently asked questions
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row, including rows full of NULLs. COUNT(column) counts only rows where that column is not NULL, so it can return a smaller number.
Is COUNT(1) faster than COUNT(*)?
No. Both count every row and most databases optimize them the same way. In our SQLite test COUNT(*) was faster because of a special fast path, so prefer COUNT(*) for clarity.
Does COUNT include NULL values?
COUNT(*) counts rows with NULLs. COUNT(column) and COUNT(DISTINCT column) skip NULL values entirely.
How do I count rows that match a condition?
Use COUNT(*) with a FILTER clause where supported, or SUM with a CASE expression that returns 1 for matching rows and 0 otherwise. Both work in a single pass over the table.
Why does my LEFT JOIN count show 1 instead of 0?
The LEFT JOIN keeps one row with NULLs for parents that have no children, and COUNT(*) counts that row. Count a column from the joined table, such as its id, to get 0.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.