COUNT star versus COUNT column in a customer report

COUNT(*) counts rows. COUNT(column) counts rows where that expression is not NULL. COUNT(DISTINCT column) counts different non-NULL values. The correct choice follows from what you want to count: records, observed attributes or unique entities.

The distinction becomes important in customer reporting because one customer can have several orders, missing contact details and multiple item lines within an order. Those are different quantities, even when they appear in the same joined table.

Use the commerce SQL lab to run the examples. It contains seven known customers and ten order headers. The records are synthetic and intentionally include missing values and an unmatched customer identifier.

Count customers and contact completeness separately

SELECT
    COUNT(*) AS customer_records,
    COUNT(email) AS records_with_email
FROM customers;

The result is seven customer records and five non-NULL emails. Subtracting the second count from the first shows two missing email values.

This does not prove that five addresses are deliverable, unique or consented for communication. It establishes only that the expression is non-NULL. An empty string is also non-NULL, so a real ingestion process must define whether blank and whitespace-only values are normalized to NULL.

The fixture already uses NULL for missing emails. If you change that convention, update the quality check rather than expecting COUNT to understand the difference between a usable address and a present string. The engine's aggregate reference describes the precise counting behaviour. SQLite aggregate functions.

Count orders without calling them customers

SELECT
    COUNT(*) AS completed_orders,
    COUNT(DISTINCT customer_id) AS customer_ids_on_completed_orders
FROM orders
WHERE status = 'completed';

There are eight completed orders and six distinct customer identifiers on those orders. Two customers ordered twice, so the counts cannot be interpreted interchangeably.

One of the six identifiers is C999, whose customer dimension record is missing. Therefore, “six verified customers” would overstate what the query establishes. A precise label is “six distinct customer IDs on completed orders, including one unmatched ID.” The quality issue should be investigated separately.

This is a useful habit: name a measure after what the query actually observes. Do not give it a more ambitious business label unless the required identity and eligibility rules have been checked.

Understand COUNT after a LEFT JOIN

Suppose you need the completed-order count for every known customer, including those with none:

SELECT
    c.customer_id,
    COUNT(o.order_id) AS completed_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'completed'
GROUP BY c.customer_id
ORDER BY c.customer_id;

Expected counts are C001: 2, C002: 2, C003: 0, C004: 1, C005: 1, C006: 1 and C007: 0. We count the right-side order key because it is NULL on the placeholder row for a customer with no qualifying order.

If you replace COUNT(o.order_id) with COUNT(*), those unmatched customers receive a count of one. The LEFT JOIN preserves a row for each customer; COUNT star counts that preserved row, even when it contains no matched order.

Notice where the status condition appears. Placing it in the JOIN condition controls which orders match while retaining all customers. A right-side status condition in WHERE can remove the unmatched rows and defeat the report's purpose.

DISTINCT can count entities, but it cannot repair every join

After joining orders to item lines, COUNT(*) counts joined lines. COUNT(DISTINCT o.order_id) can recover the number of represented orders. That may be appropriate for a product report where an order with several qualifying lines should count once.

However, it does not prove that every eligible order survived the join. If some orders have no item rows and you used an INNER JOIN, their IDs have disappeared before the distinct count is calculated. You need an unmatched-record check as well.

Likewise, distinct counting does not repair an inflated monetary sum. The row-grain lesson demonstrates why summing distinct amounts can undercount separate orders with equal values.

Build a small count specification

For each count in a report, record these decisions:

This specification is more durable than a rule such as “always use DISTINCT.” Sometimes repeated rows are errors; sometimes they are legitimate events. The query needs to reflect the intended entity.

Exercise: write a report with known customers, customers with at least one completed order and customers with no completed orders. The correct counts are seven, five and two. Explain why the distinct customer-ID count from the order table was six: it includes C999, which is outside the known-customer table.

NeuraPath's Data Analytics with Generative AI course develops this kind of metric reasoning alongside SQL and reporting tools. Reliable counts are especially valuable when reviewing AI-generated queries, where a fluent explanation can conceal a change in the counted entity.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL NULL values: why missing is not zero.
• Continue with INNER JOIN versus LEFT JOIN with unmatched customers.