COUNT in SQL, Explained for Beginners

The three forms of COUNT and what each one counts

Picture one small table, customers, with a region column where two rows were never filled in:

Now run the three forms on it:

SELECT
  COUNT(*)                 AS all_rows,
  COUNT(region)            AS rows_with_region,
  COUNT(DISTINCT region)   AS different_regions
FROM customers;

• COUNT(*) = 5. Every row, no exceptions. The * means "the row itself," so NULLs never matter.
• COUNT(region) = 3. Only the rows where region has a value. Sam and Taylor are skipped because their region is NULL.
• COUNT(DISTINCT region) = 2. The different values are just North and South. The two Norths collapse to one, and NULL is not counted.

The NULL rule that makes them disagree

Here is the whole trick in one line:

COUNT(*) counts rows. COUNT(something) counts non-NULL values of that something.

So the moment a column has any NULLs, COUNT(column) comes back smaller than COUNT(*). That gap is not a bug, it is information: it is exactly how many rows are missing a value in that column.

SELECT COUNT(*) - COUNT(region) AS missing_regions
FROM customers;   -- returns 2

One more consequence people forget: because COUNT(column) ignores NULLs, COUNT(1) and COUNT(*) are the same thing (the constant 1 is never NULL), but COUNT(a_column_full_of_nulls) can be 0. When you just want "how many rows," reach for COUNT(*) and never think about it again.

COUNT(*) vs COUNT(DISTINCT): the difference interviewers ask about

This is the classic recall question, and it is worth being able to answer without hesitating:

When those numbers differ, you have learned something. If COUNT(*) is bigger than COUNT(DISTINCT order_id), some order_id appears more than once, meaning you have duplicate rows. That single comparison is the fastest duplicate check in SQL:

SELECT
  COUNT(*)                  AS total_rows,
  COUNT(DISTINCT order_id)  AS unique_orders,
  COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows
FROM orders;

If duplicate_rows is 0, every order is listed once. If it is anything above zero, that is how many extra copies are hiding in the table.

COUNT with GROUP BY: counting per group

On its own, COUNT gives one number for the whole table. Add GROUP BY and it gives one number per group instead. GROUP BY sorts the rows into bins by the column you name, and COUNT runs inside each bin:

SELECT region, COUNT(*) AS customers
FROM customers
GROUP BY region
ORDER BY customers DESC;

A subtle but important pairing: COUNT(*) counts every row in the group, while COUNT(column) counts only the rows in the group where that column is filled in. Grouping customers by region and reading COUNT(*) next to COUNT(email) tells you, per region, how many customers you have and how many of them you can actually email.

How analysts use COUNT in a data migration

When data moves from one place to another (a CSV into a database, an old system into a new one, a raw table into a cleaned one), COUNT is the first thing you run on both sides. It is the cheapest way to answer "did everything arrive, and did anything sneak in twice?" Three checks cover most of it.

SELECT COUNT(*) FROM customers_raw;   -- the imported original
SELECT COUNT(*) FROM customers;       -- the cleaned copy

SELECT
  COUNT(*)                     AS rows,
  COUNT(DISTINCT customer_id)  AS unique_ids
FROM customers;

SELECT
  COUNT(*)                                       AS rows,
  COUNT(email)                                   AS has_email,
  ROUND(100.0 * COUNT(email) / COUNT(*), 1)      AS email_pct
FROM customers;

Using COUNT on your own portfolio project

Every dataset you load for a portfolio project deserves the same opening move. Before you write a single insight, run COUNT to understand what you are holding. On a games dataset, for example:

-- How big is it, and how many distinct studios and genres?
SELECT
  COUNT(*)                  AS games,
  COUNT(DISTINCT developer)  AS studios,
  COUNT(DISTINCT genre)      AS genres
FROM games;

-- Which columns are patchy? (compare each to the row count)
SELECT
  COUNT(*)             AS rows,
  COUNT(price)         AS has_price,
  COUNT(release_date)  AS has_date
FROM games;

Leading a project write-up with "the dataset has 83,201 games across 41 genres, and 6% are missing a release date" reads like an analyst, not a beginner. It also protects you: knowing where the NULLs are stops you from quietly under-counting later. This "count first, then analyze" habit is exactly what the Exploratory Data Analysis guide leans on, and what the Documenting Data Limitations guide turns into a written record.

A COUNT cheat sheet