COUNT(*) vs COUNT(column)

COUNT(*) counts the rows in the group. COUNT(column) counts the rows where that column is not NULL. When the column is declared NOT NULL the two are always identical, and when the column is nullable they can differ.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  assigned_to INTEGER
);

INSERT INTO tickets (id, title, assigned_to) VALUES
  (1, 'Login page times out',  11),
  (2, 'Invoice PDF is blank',  12),
  (3, 'Export times out',     NULL),
  (4, 'Dark mode request',    NULL),
  (5, 'Slow search results',  11);

SELECT COUNT(*) AS all_rows,
       COUNT(assigned_to) AS assigned_rows,
       COUNT(*) - COUNT(assigned_to) AS unassigned_rows
FROM tickets;

Three rows come back in one line: 5, 3 and 2. There are five tickets, three of them have an assignee, and two are unassigned. COUNT(assigned_to) is 3 rather than 5 because NULL values are not counted.

The difference matters most in three situations. When you join with an outer join, the outer-joined rows arrive with NULLs in the right-hand columns, so COUNT(*) counts them and COUNT(right.column) does not. When you are checking data completeness, COUNT(column) is the tool that tells you how many values are actually present. And when you compute an average, the denominator is the count of non-NULL values, not the row count.

Two more details. COUNT(DISTINCT column) is a third thing entirely: it counts distinct non-NULL values, which is how you find out how many different assignees there are. And the performance difference is real but usually small — COUNT(*) can be answered by an index-only scan, while COUNT(column) has to read the column itself unless the index covers it. On a very wide table that can matter.

The rule to remember: COUNT(*) asks how many rows, COUNT(column) asks how many values. Choose deliberately, because the gap between them is exactly the number of missing values, which is often the number the report is trying to surface.

This is where the distinction stops being academic. An outer join keeps the unmatched row from the left table and fills the right-hand columns with NULL. Counting rows therefore includes the customers with no orders; counting the right-hand column does not.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

SELECT c.id,
       c.name,
       COUNT(*) AS rows_in_group,
       COUNT(o.id) AS order_count,
       ROUND(COALESCE(SUM(o.amount), 0), 2) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Three rows come back. Ada Lovelace has 2 rows in her group and 2 orders, with 460.50 of revenue. Grace Hopper has 1 row and 1 order, with 75.25. Alan Turing has 1 row in his group but 0 orders, because the outer join gave him a row with NULL in every orders column, and his revenue shows as 0.00 thanks to the COALESCE.

Read the Alan Turing row carefully — it is the whole lesson. COUNT(*) says 1, which is the count of rows in the group, and it is not wrong; there really is one row. COUNT(o.id) says 0, which is the count of actual orders. If you had used COUNT(*) to display "number of orders", every customer would show at least 1, and the customer with no orders would be reported as having exactly one.

Notice also that SUM(o.amount) for Alan Turing is NULL, not 0, because SUM ignores NULLs and there is nothing to add. That is why the COALESCE is there. A report that shows blank for a customer with no orders and a report that shows 0.00 are making different choices, and it is worth deciding which one the business expects.