By the end of this lesson you can answer “how many …?” questions with COUNT(*), COUNT(column) and COUNT(DISTINCT column), and explain why the three can give different numbers for the same table.
Why it matters: “How many?” is the first question in almost every report: how many customers, how many orders this year, how many of them used a coupon. Picking the wrong kind of COUNT is one of the most common reasons two people's numbers disagree.
Dataset: Wadi Market online store. Examples run in the editor on the right.
Until now every query returned one result row per table row (or fewer, after a WHERE). Reporting needs a different kind of answer: not the rows themselves, but a fact about the rows. How many customers do we have? The answer is one number.
SQL produces such answers with aggregate functions. An aggregate reads many rows and returns a single value that summarises them. The simplest one is COUNT. When a query's SELECT list contains only aggregates, PostgreSQL treats the whole table (after WHERE) as one pile of rows and returns exactly one result row describing that pile.
SELECT COUNT(*) -- every row
, COUNT(column) -- rows where column IS NOT NULL
, COUNT(DISTINCT column) -- different non-NULL values
FROM table_name
WHERE condition; -- optional: filter first, then countSELECT COUNT(*) AS customers
FROM customers;PostgreSQL reads all 800 rows of customers, counts them and returns one row with one column. The alias gives the number a readable name; without it the column would be called count.
COUNT(*) versus COUNT(column)
COUNT(*) counts rows. It does not look at any column, so it can never be fooled by missing values.
COUNT(column) counts the rows where that column has a value: it skips NULLs. That makes it a quick way to measure how complete a column is. Marketing asks “how many customers can we email?”: that is COUNT(email), not COUNT(*).
SELECT COUNT(*) AS customers,
COUNT(email) AS with_email,
COUNT(phone) AS with_phone,
COUNT(city) AS with_city
FROM customers;Still one row, but four counts over the same 800 rows. with_email (758) and with_phone (765) are lower than customers because those columns are NULL for some people. The difference, for example 800 − 758 = 42, is the number of customers with no email on file.
COUNT(DISTINCT column)
The orders table has one row per order, and most customers order more than once. How many customers have ordered? is therefore not COUNT(*) (that counts orders) and not COUNT(customer_id) (that also counts orders, because customer_id is never NULL). It is the number of different customer_id values: COUNT(DISTINCT customer_id). PostgreSQL collects the values, removes duplicates and NULLs, and counts what is left.
SELECT COUNT(*) AS orders,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT coupon_code) AS coupon_spellings,
COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;6,221 orders, of which 1,004 used a coupon (coupon_code is NULL otherwise). Only 630 different customers placed all those orders. coupon_spellings is 19, more than the real number of campaigns, because customers typed some codes in lower case or with a trailing space: DISTINCT compares exact text. You'll clean that up in a later lesson.
Filter first, then count
WHERE runs before the aggregate. The database first keeps the rows that pass the condition, then counts them. So “how many orders were cancelled?” is a WHERE plus a COUNT:
SELECT COUNT(*) AS cancelled_orders
FROM orders
WHERE status = 'cancelled';If no row passes the filter, COUNT returns 0, not an empty result: an aggregate query without GROUP BY always returns exactly one row.
SELECT COUNT(*) AS orders_2025,
COUNT(DISTINCT customer_id) AS customers_2025
FROM orders
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2026-01-01';The WHERE keeps only orders placed in 2025 (a half-open range, so every moment of 31 December is included). Then the two counts run over those rows only: 2,721 orders from only 521 different customers, so the average active customer ordered about five times that year.