COUNT

COUNT() is an aggregate function that returns a BIGINT count of rows in a group, or of the whole result set when there is no GROUP BY. It comes in three forms that answer three different questions, and confusing them is the most common aggregation bug.

The three forms

• COUNT(*) counts rows retrieved, including rows whose columns are all NULL.
• COUNT(expr) counts rows where expr is not NULL. COUNT(NULL) is 0.
• COUNT(DISTINCT expr [, expr ...]) counts rows with different non-NULL values, or different combinations of values when given several expressions.

COUNT(1) is the same thing as COUNT(*): the literal 1 is never NULL, so every row qualifies. They are interchangeable — prefer COUNT(*), which is the form the manual documents.

What trips people up

COUNT returns 0 for an empty set, while SUM, AVG, MIN and MAX return NULL. COUNT never needs a COALESCE wrapper; the others often do.

On a LEFT JOIN, COUNT(*) still counts the unmatched row — the row exists, only its right-hand columns are NULL. Count a column from the right-hand table instead to get a true zero.

InnoDB stores no row counter. COUNT(*) on a big table scans an index and counts only rows visible to your transaction, so two concurrent sessions can legitimately report different totals.

-- Per customer: total orders, how many were actually shipped,
-- and how many distinct products they have ever bought.
SELECT
    c.id,
    c.email,
    COUNT(*)                        AS orders_total,     -- every row in the group
    COUNT(o.shipped_at)             AS orders_shipped,   -- rows where shipped_at IS NOT NULL
    COUNT(DISTINCT oi.product_id)   AS distinct_products -- unique, NULLs skipped
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id   = o.id
GROUP BY c.id, c.email
ORDER BY orders_total DESC;   -- MySQL 8.4 no longer sorts GROUP BY implicitly

-- WRONG: customers with no orders come back with 1, not 0.
SELECT c.id, COUNT(*) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;

-- RIGHT: count a column from the outer-joined table.
-- For an unmatched customer, o.id is NULL, so the count is 0.
SELECT c.id, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;

-- Shorthand: distinct combinations of two columns, in one pass.
SELECT COUNT(DISTINCT customer_id, ship_country) AS customer_country_pairs
FROM orders;

-- Longhand: the same number via a derived table.
-- Both skip rows where either expression is NULL.
SELECT COUNT(*) AS customer_country_pairs
FROM (
    SELECT DISTINCT customer_id, ship_country
    FROM orders
    WHERE customer_id IS NOT NULL
      AND ship_country IS NOT NULL
) AS pairs;

-- Fast, approximate: comes from the optimizer's statistics, not the data.
EXPLAIN SELECT * FROM events;

-- Exact, and it scans: InnoDB must count rows visible to this transaction.
SELECT COUNT(*) AS event_rows FROM events;

-- Cheaper exact count of a subset, if (created_at) is indexed:
SELECT COUNT(*) AS events_today
FROM events
WHERE created_at >= CURRENT_DATE;