COUNT

COUNT is the aggregate function you will reach for most often — it tells you how many rows match a condition. But COUNT has three distinct forms, and mixing them up is one of the most common sources of subtly wrong query results in SQL.

The three forms of COUNT

Form	What it counts
COUNT(*)	Every row in the group, regardless of NULLs in any column
COUNT(column)	Only rows where that specific column is NOT NULL
COUNT(DISTINCT column)	The number of unique, non-NULL values in that column

COUNT(*) vs COUNT(column) is a classic gotcha

COUNT(*) counts rows — full stop. It does not look inside any column, so rows with NULL values are still counted. COUNT(column) is different: it only counts rows where that particular column has an actual (non-NULL) value. This distinction quietly produces wrong totals if you assume they always give the same number, and it is a frequent interview question for exactly that reason.

Worked example

customers

SQL

-- customers table
-- id | name    | phone
-- ---+---------+-----------
--  1 | Alice   | 555-0101
--  2 | Bob     | NULL
--  3 | Carol   | 555-0101
--  4 | Dave    | 555-0104
--  5 | Eve     | NULL

SELECT
  COUNT(*)                 AS total_rows,
  COUNT(phone)              AS rows_with_phone,
  COUNT(DISTINCT phone)     AS unique_phone_numbers
FROM customers;

total_rows | rows_with_phone | unique_phone_numbers
-----------+-----------------+----------------------
         5 |               3 |                    2

All three numbers come from the same five rows, and all three are correct — they are just answering different questions:

COUNT(*) = 5 — there are 5 rows total, NULL phones included.
COUNT(phone) = 3 — only 3 rows have a non-NULL phone number.
COUNT(DISTINCT phone) = 2 — of those non-NULL phone numbers, only 2 are unique (555-0101 appears twice).

COUNT with WHERE and GROUP BY

SQL

-- Count only delivered orders
SELECT COUNT(*) AS delivered_count
FROM orders
WHERE status = 'delivered';

-- Count orders per customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

-- Count how many distinct customers have placed an order
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

A useful mental model: COUNT(*) answers "how many rows are there?", COUNT(column) answers "how many rows actually have a value here?", and COUNT(DISTINCT column) answers "how many different values are there?" Reach for the one that matches the actual question you are asking.

COUNT(*) counts all rows, including rows with NULL values.
COUNT(column) counts only rows where that column is non-NULL.
COUNT(DISTINCT column) counts unique non-NULL values.
Choosing the wrong form is a very common source of subtly incorrect results.