COUNT()

Syntax

How it works

COUNT() is one of the five core SQL aggregate functions, alongside SUM(), AVG(), MIN() and MAX(). It collapses many rows into a single number: how many rows are in the group.

The behaviour depends on what you pass to it. COUNT(*) counts every row, whether or not its columns are NULL. COUNT(column) counts only the rows where that column is not NULL, so it is a quick way to find how many rows actually have a value. COUNT(DISTINCT column) counts how many different non-NULL values exist.

Like every aggregate, COUNT() is normally paired with GROUP BY to produce one count per group, and filtered after aggregation with HAVING rather than WHERE. Since SQL:2003 it can also be used as a window function with an OVER() clause, which keeps every row instead of collapsing them.

Examples

Count all rows in a table

SELECT COUNT(*) AS total_orders
FROM orders;

total_orders
------------
1543

Count non-NULL values in a column

-- phone is NULL for customers who never gave one
SELECT COUNT(*)     AS all_customers,
       COUNT(phone) AS have_phone
FROM customers;

all_customers | have_phone
--------------+-----------
         600  |       412

Count distinct values

SELECT COUNT(DISTINCT country) AS countries
FROM customers;

countries
---------
       27

Count per group with GROUP BY

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

country | customers
--------+----------
 UAE    |       210
 KSA    |       143
 EGY    |        98

Conditional count with CASE

-- how many orders are paid vs unpaid, in one pass
SELECT
  COUNT(*)                                        AS total,
  COUNT(CASE WHEN status = 'paid' THEN 1 END)     AS paid,
  COUNT(CASE WHEN status <> 'paid' THEN 1 END)    AS unpaid
FROM orders;

total | paid | unpaid
------+------+-------
 1543 | 1301 |    242

Running count as a window function

SELECT id, created_at,
       COUNT(*) OVER (ORDER BY created_at) AS orders_so_far
FROM orders
ORDER BY created_at;

id | created_at | orders_so_far
---+------------+--------------
 1 | 2026-01-02 |            1
 2 | 2026-01-02 |            2
 3 | 2026-01-05 |            3

Common mistakes

-- Counts only rows where bonus is NOT NULL,
-- which is almost never what people expect here
SELECT COUNT(bonus) FROM employees;

-- Count every employee, regardless of NULLs
SELECT COUNT(*) FROM employees;

-- Aggregates are not allowed in WHERE
SELECT country, COUNT(*)
FROM customers
WHERE COUNT(*) > 100
GROUP BY country;

SELECT country, COUNT(*)
FROM customers
GROUP BY country
HAVING COUNT(*) > 100;

-- Returns 1 (one row of counts), not 0, for an
-- empty table when you expected "no groups"
SELECT COUNT(*) FROM orders WHERE 1 = 0;

-- COUNT(*) with no GROUP BY always returns one row.
-- It is 0 here, which is correct; just be aware a
-- row is always produced.
SELECT COUNT(*) FROM orders WHERE 1 = 0;  -- 0

Performance

COUNT(*) is heavily optimised. On many engines it can be answered from an index rather than by reading the whole table, and MySQL InnoDB keeps counts cheap through secondary indexes. If you only need to know whether any row exists, prefer EXISTS over COUNT(*) > 0, because EXISTS can stop at the first matching row.

COUNT(DISTINCT expr) is far more expensive: the database must track every distinct value, which usually means a sort or a hash and more memory. On large tables consider an approximate count where your engine offers one, such as APPROX_COUNT_DISTINCT in SQL Server or COUNT(DISTINCT ...) backed by HyperLogLog extensions in PostgreSQL.

A covering index on the columns you filter and group by lets the optimiser answer counts without touching the base table. See the indexing guide for how index-only scans work.

Interview questions

SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid
FROM orders;