SQL COUNT: rows, NULL, DISTINCT and JOIN pitfalls

COUNT answers a very short question: how many? Trouble starts when people mean different things. One person counts customers, another counts orders, and a third counts the rows produced by joining orders to products. Every query can run successfully. The numbers still refuse to agree.

I start by deciding what one row represents. Then I choose the counting expression and check a small result by hand. I will use synthetic data to show why an empty email can be counted, why a customer without purchases can receive a one, and how order items can inflate an order count.

I checked all the SQL blocks and output tables below in SQLite 3.53.1 through node:sqlite in Node 24.18.0. Run them in order within the same connection. Notes about other engines come from their documentation; they do not imply that I executed these examples on those engines.

What the three COUNT variants count

COUNT(*) counts every input row. COUNT(email) counts rows where the expression email is not NULL. COUNT(DISTINCT email) counts different values of that expression, excluding NULL. These rules are described in the SQLite aggregate documentation.

I deliberately say input rather than always saying table. A filter, grouping operation or join will change the input in later examples. The function counts what it receives. It does not check whether an order has already been repeated three times along the way.

The argument can be an expression, not just a column. COUNT(1) counts a non-null one for every row, giving the same result as the star. COUNT(0) also counts every row because zero is not NULL. Meanwhile, COUNT(NULL) produces zero. A number inside the parentheses does not select a column by its position or specify a counting interval.

When I need a row count, I prefer the star because it makes the intention easy to read. For field completeness, I name the field. For distinct business objects, I first establish their identifier. Counting different customer names is rarely a reliable definition of the number of customers.

One dataset for every example

Run this setup once in an empty test connection. It creates three temporary tables. customers contains one row per customer, orders one per order, and order_items one per order item. Those are three different levels of detail. Writing them down before counting saves a surprising amount of confusion later.

The names, addresses in the example.com domain and statuses are fictional. Two accounts share an email, one has NULL, and another has an empty string. An order can have an unknown status. Dan has no orders at all. I have deliberately included these cases because a perfectly clean example does little to expose reporting mistakes.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);
INSERT INTO customers VALUES
    (1, 'Ada', 'ada@example.com'),
    (2, 'Ben', NULL),
    (3, 'Celina', ''),
    (4, 'Dan', 'shared@example.com'),
    (5, 'Ewa', 'shared@example.com');

CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT
);
INSERT INTO orders VALUES
    (101, 1, 'paid'),
    (102, 1, 'cancelled'),
    (103, 2, 'paid'),
    (104, 3, NULL),
    (105, 5, 'paid'),
    (106, 5, 'paid');

CREATE TEMP TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product TEXT NOT NULL
);
INSERT INTO order_items VALUES
    (1001, 101, 'Book'),
    (1002, 101, 'Course'),
    (1003, 102, 'Book'),
    (1004, 103, 'Course'),
    (1005, 104, 'Book'),
    (1006, 105, 'Book'),
    (1007, 105, 'Course'),
    (1008, 105, 'Workbook'),
    (1009, 106, 'Course');

Now I compare the three variants on the same table:

-- example: variants
SELECT COUNT(*) AS customer_rows,
       COUNT(email) AS nonnull_emails,
       COUNT(DISTINCT email) AS distinct_emails
FROM customers;

Five is the number of accounts. Four is the number of rows whose email is not NULL. Three is the number of different email values, including the empty string. The data did not change. The question did. In a report, I therefore give these measures descriptive names instead of labelling all three simply “customers”.

NULL, empty strings and data quality

In this dataset, an empty string is a value. That does not mean it is a usable destination for a message. COUNT(email) does not validate address syntax or whitespace. It would also count the word missing. A result of four therefore does not prove that four customers have usable contact details.

I can define a narrower measure: addresses that are not empty after removing ordinary leading and trailing spaces. I use TRIM and NULLIF to turn an empty string into NULL. Notice the output name. I still do not call this a count of valid addresses.

-- example: blanks
SELECT COUNT(NULLIF(TRIM(email), '')) AS nonblank_emails,
       COUNT(*) - COUNT(email) AS null_emails
FROM customers;

The first number still includes both accounts sharing the same address. The second measures only NULL, excluding empty strings. If I presented them without definitions, a reader might subtract both from five and mistake the remaining account for a reporting error. The categories are answering different questions.

In practice, I record total rows, missing values and values passing an agreed check separately. I also investigate why a value is missing before cleaning it. An optional field, a broken import and a technical account can look identical in one column while requiring different decisions. An aggregate cannot make those decisions for me.

WHERE selects rows and GROUP BY forms groups

Four orders have the status paid. If those are the only orders I need, I filter the input immediately:

-- example: where
SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid';

To see all orders broken down by customer, I group the rows. Each group gets its own counter. I also check how many statuses are known:

-- example: groups
SELECT customer_id,
       COUNT(*) AS order_rows,
       COUNT(status) AS known_statuses
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Customer three has an order even though its status is unknown. Customer four does not appear at all because the orders table contains no row with that identifier. Grouping does not know the complete customer list and cannot invent the missing categories.

This matters when building a chart. A missing bar can mean that the result contains no row, while zero can be an actual measure value. To display every person, region or calendar date, I need a complete list of those objects and an appropriate join. We will return to that shortly.

I also add an explicit final ORDER BY. A result that happens to arrive in customer order during a small test is not a substitute for requesting that order. Stable presentation makes comparisons easier, particularly when checking the same example in a different database client.

HAVING filters the counted groups

Now I want customers with at least two orders. I form the groups first, then inspect their size:

-- example: having
SELECT customer_id, COUNT(*) AS order_rows
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Adding WHERE status = 'paid' before the grouping would change the question to customers with at least two paid orders. Ada would no longer qualify because one of her orders is cancelled. Filter placement therefore affects the business meaning even when the two queries look almost identical.

I like checking a threshold against an actual record. “At least two” means >= 2, while “more than two” means > 2. On our data, the second condition returns an empty result. That distinction can disappear in a conversation, but the code must make it explicit. The HAVING entry contains more examples of filtering groups.

Empty input: zero versus no rows

Consider a filter that no order satisfies. An ordinary aggregate without grouping still returns one row:

-- example: empty
SELECT COUNT(*) AS order_rows,
       COUNT(status) AS known_statuses,
       COUNT(DISTINCT status) AS distinct_statuses
FROM orders
WHERE order_id < 0;

Adding grouping changes the shape of the result:

-- example: empty-groups
SELECT customer_id, COUNT(*) AS order_rows
FROM orders
WHERE order_id < 0
GROUP BY customer_id
ORDER BY customer_id;

The table above has column headers and zero data rows. It is not a single record with a missing identifier. No groups were formed. This distinction matters to an application trying to read the first result and to a dashboard expected to display zero. The SQLite SELECT documentation distinguishes these cases.

I do not automatically add COALESCE(COUNT(*), 0), because counting an empty input already gives zero. When a group has no result row, replacing a value inside that nonexistent row does not create it. A HAVING clause can also discard an aggregate row, so the presence of COUNT does not always guarantee one output record.

Before changing an empty chart to show a zero, I therefore ask whether the source was successfully read and whether the category belongs in the report. A failed data load and a legitimate absence of orders deserve different explanations, even if the screen initially looks equally empty.

Conditional counting and the ELSE 0 trap

I often need paid orders, cancelled orders and orders with an unknown status side by side. Several counters can share one query:

-- example: conditional
SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
       COUNT(CASE WHEN status IS NULL THEN 1 END) AS unknown_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS wrong_paid_count
FROM orders;

The final column deliberately uses the wrong expression for counting paid orders. Its CASE returns either one or zero. Both values are non-null, so COUNT counts all six rows. Omitting ELSE in the first expression produces NULL for the other cases, which is why that counter works.

Adding zeros makes sense in SUM(CASE ... THEN 1 ELSE 0 END), but copying that structure blindly into COUNT changes the meaning. Remember that SUM also behaves differently on empty input. I cover that separately in the SUM entry.

SQLite offers a shorter spelling with FILTER:

-- example: filter
SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders;

I choose a form that fits the engine and the project’s conventions. I do not assume FILTER support is identical across databases. With either spelling, I check how an unknown status and a future status value would be treated. A new category should not silently disappear from a measure whose name promises to cover everything.

LEFT JOIN: count matches and keep the zeros

Now I want all five customers, including Dan with no orders. The customers table must therefore be the starting point:

-- example: left-join
SELECT c.customer_id,
       COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

Dan’s row survives the join, with NULL in the order columns. The star counts that preserved row, while the order identifier skips it. To count matches, I therefore choose a key that is always populated for a real order. COUNT(o.status) would be a poor substitute because it would miss Celina’s existing order.

For the number of paid orders, I put the status condition into the matching rule:

-- example: paid-left-join
SELECT c.customer_id, COUNT(o.order_id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY c.customer_id;

Moving it to WHERE o.status = 'paid' would remove customers without a matching paid order. Sometimes that is the desired result, but it no longer gives the complete customer list with zeros. The separate LEFT JOIN entry explores that distinction with further examples.

A useful manual check is to follow one unmatched customer through the query. Write the row produced by the join, including its null order columns, and evaluate the counting expression on that row. It is much easier to explain the unexpected one when you can point to the exact row being counted.

DISTINCT after JOIN: a useful tool with limits

Next I join orders to their items. Order 101 has two items and order 105 has three. Each appears repeatedly in the joined result:

-- example: multiplied
SELECT COUNT(*) AS item_rows,
       COUNT(o.order_id) AS repeated_order_ids,
       COUNT(DISTINCT o.order_id) AS distinct_orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;

Nine is the correct number of items. Six is the correct number of orders represented in this join. DISTINCT works here because the identifier represents the object I actually want to count. Counting different statuses instead would answer an entirely different question.

I do not use DISTINCT as a universal repair. Another join might have removed some orders, and deduplication will not bring them back. Every order in our fixture has an item, but a real system might contain drafts without items. I first check the relationship and join type, then choose the counter.

If the question is how many orders have at least one item, I can avoid multiplying rows in the first place:

-- example: exists
SELECT COUNT(*) AS orders_with_items
FROM orders AS o
WHERE EXISTS (
    SELECT 1 FROM order_items AS i WHERE i.order_id = o.order_id
);

EXISTS expresses the need for a match directly. I am not promising it will always run faster. I choose it for clarity of meaning, then inspect the actual plan and data to assess performance. Correctness and speed are separate questions, and that is the order in which I prefer to answer them.

Distinct pairs and counts beside detail rows

What if I need the number of different customer and status pairs? Instead of concatenating values into a string, I select distinct pairs and count the resulting rows:

-- example: distinct-pairs
SELECT COUNT(*) AS customer_status_pairs
FROM (
    SELECT DISTINCT customer_id, status FROM orders
) AS pairs;

Customer three’s pair with NULL is included. Ewa’s two paid orders form one pair. That definition should be intentional. If unknown statuses must be excluded, put the filter inside the subquery. Concatenating strings can introduce key collisions and mix missing-value handling with formatting choices.

To keep one row per order while adding the customer’s order count, I use a window function:

-- example: window
SELECT order_id, customer_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders
ORDER BY order_id;

The result still contains six rows. Do not later sum customer_orders as the number of orders: the count is repeated beside the detail records. The final ORDER BY controls presentation. I deliberately do not put an ordering clause inside OVER, where it could change the counting frame. The SQLite window documentation explains the syntax and restrictions, including the lack of support for DISTINCT inside a window function.

Dialects and performance without magic promises

The basic variants are similar across many databases, but return types and extensions need checking. PostgreSQL 18 returns bigint. In SQL Server, COUNT returns int and COUNT_BIG returns bigint. This matters for large datasets because an ordinary SQL Server count can exceed its return type’s range.

MySQL 8.4 documents multi-expression COUNT(DISTINCT ...), excluding combinations containing NULL. I do not copy that spelling into SQLite. The distinct-pairs subquery above has explicit semantics, including its treatment of the unknown status.

I also do not assume that changing the star to a one or adding an index automatically makes counting faster. I ask which filter applies, how much input must be read, and whether a join creates unnecessary repetition. In SQLite, I start with EXPLAIN QUERY PLAN, which shows how indexes are used, then measure a representative query. Five customers are enough to test an interpretation, but cannot predict execution time on millions of records.

When comparing two reports, I also check when the data was read. If orders were added between the queries, different counts may correctly describe different data states. I keep the filter, object definition and time scope alongside the result. A label saying only “COUNT” is insufficient to reproduce the analysis.

Exercise: how many customers actually bought

Using the same data, count all customers, customers with at least one paid order, and customers without a paid order. Count Ewa once even though she bought twice. Put Celina and Dan in the final category. Do not delete orders or modify the fixture to make the question easier.

Write the expected result by hand first. Then try to produce the list of buyer identifiers. Only count the list once you understand who belongs in it. If you get four buyers, you have probably counted orders instead of customers. That is a useful mistake to make on six records before it reaches a presentation.

One solution reduces paid orders to one row per customer before joining:

-- example: exercise
WITH buyers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'paid'
)
SELECT COUNT(*) AS customers,
       COUNT(b.customer_id) AS buyers,
       COUNT(*) - COUNT(b.customer_id) AS nonbuyers
FROM customers AS c
LEFT JOIN buyers AS b ON b.customer_id = c.customer_id;

Consider two changes: another paid order from Ewa, and Dan’s first paid order. The first should leave the buyer count unchanged. The second should increase buyers by one and decrease nonbuyers by one. Those boundary checks tell you much more than the fact that your query ran without an error.

When you use COUNT in your own report, name the object, inspect NULL, look at a few rows after joins and compare the result with a hand calculation. That short process helps the number in a table mean what its heading actually promises.