1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL COUNT: COUNT star vs COUNT column vs COUNT DISTINCT explained

Key takeaways

  • 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
Software Development

301 vs 302 Redirect: Differences, SEO Impact, 307 and 308

301 vs 302 redirect: the differences, SEO impact, 307 and 308, method changes, redirect chains and loops, all tested on…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Databases & SQL

SQL Random Rows: How to Select Random Rows Fast and Fairly

SQL random rows: how to select random rows fast and fairly, timing ORDER BY RANDOM, offsets and ID jumps, and…

Read article →
Databases & SQL

SQL Median: 4 Ways to Calculate the Median, With Tested Queries

SQL median explained: four tested ways to calculate the median, including percentile functions, window ranks, offsets and per group medians.

Read article →
Software Development

JavaScript Regex Explained: Patterns, Flags, Groups and Pitfalls

JavaScript regex explained: literals, flags, groups, named groups, lookarounds, matchAll, replace and the catastrophic backtracking trap, tested in Node.

Read article →
Software Development

Python property: Getters, Setters, Validation and cached_property

Python property explained: getters, setters, deleters, validation, read-only and computed attributes, and cached_property, with tested code.

Read article →
Software Development

Python Enum Explained: auto, IntEnum, StrEnum, Flag and Best Practices

Python enum explained with tested examples: Enum, auto, IntEnum, StrEnum, Flag, aliases, unique, JSON serialisation and match statements.

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
Software Development

401 vs 403: Unauthorized vs Forbidden, and When to Use Each

401 vs 403 explained: authentication vs authorization, the WWW-Authenticate header, when to return 404 instead, client reactions and a tested…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →
Databases & SQL

SQL ROUND Function: Decimals, Ties, CEIL, FLOOR and Money

SQL ROUND explained with tested queries: decimals, tie rules, the 2.675 float trap, negative digits, CEIL, FLOOR, truncation and rounding…

Read article →
Databases & SQL

Recursive CTE in SQL: Hierarchies, Paths, Series and Cycles

Recursive CTE in SQL explained with tested queries: org charts with levels and paths, walking up a tree, team sizes,…

Read article →
Software Development

JavaScript Deep Copy: structuredClone vs JSON vs Spread, Tested

JavaScript deep copy compared: spread, Object.assign, the JSON trick, structuredClone and a custom copy, tested on Dates, Maps, Sets, BigInt…

Read article →
Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →
Databases & SQL

SQL UPDATE Statement: Safe Updates, UPDATE With JOIN and RETURNING

The SQL UPDATE statement explained with tested examples: WHERE guards, CASE updates, UPDATE with JOIN, the subquery NULL trap, RETURNING…

Read article →
Databases & SQL

SQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) Explained

SQL COUNT explained: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), NULLs and empty strings, conditional counts, HAVING, the LEFT JOIN trap and…

Read article →
Artificial Intelligence

K Nearest Neighbors (KNN) Explained: How It Works, With Python Code

K nearest neighbors explained: the KNN algorithm, a from-scratch NumPy version, choosing k, feature scaling, distance metrics, regression and vector…

Read article →
DevOps

Git Rebase Interactive: Squash, Fixup, Reword and Autosquash

Git rebase interactive explained step by step: pick, reword, squash, fixup, edit, drop, autosquash, conflicts and undoing a rebase with…

Read article →
Software Testing

Alpha vs Beta Testing: Key Differences, Exit Criteria and Checklist

Alpha vs beta testing compared: who tests, what each phase finds, entry and exit criteria, how to run a beta,…

Read article →
Test Automation

Pytest Monkeypatch: 10 Tested Examples for Env Vars, Time and APIs

Pytest monkeypatch explained with 10 passing tests: environment variables, functions, config, time, sleeps, paths, blocking the network and the wrong-target…

Read article →
Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →