Aggregation Updated November 2026

COUNT

COUNT(*) returns the number of rows, COUNT(column) the number of rows where that column is not NULL, and COUNT(DISTINCT column) the number of different values. Combine it with WHERE to count matching rows and with GROUP BY to count per category.

Syntax

COUNT(*) | COUNT(column) | COUNT(DISTINCT column)

Examples

SELECT COUNT(*) AS total_orders FROM orders;

Result: total_orders 355

SELECT COUNT(*) AS rows, COUNT(manager_id) AS with_manager FROM employees;

Result: rows | with_manager 12 | 11

One employee has a NULL manager_id and is not counted by COUNT(manager_id).

SELECT status, COUNT(*) AS n FROM orders GROUP BY status;

Result: status | n paid | 310 pending | 45

Common mistakes

Expecting COUNT(column) to count all rows; it skips NULLs, so use COUNT(*) for a row count.
SELECT name, COUNT(*) FROM employees without GROUP BY: PostgreSQL errors, SQLite returns one arbitrary name next to the total.
COUNT(*) on an empty table returns 0, not NULL, but SUM returns NULL; do not assume they behave the same.

Practice COUNT

These drills check your answer on live data, free, no account needed.

Count paid ordersHow many orders have the status paid? Return a single number.
Customers per countryFor each country, return the country and how many customers it has.
Orders per statusReturn each order status and how many orders have it.

Questions

What is the difference between COUNT(*) and COUNT(1)?

Nothing in practice; both count every row and modern optimizers treat them identically. COUNT(*) is the conventional form.

How do I count rows that match a condition inside a bigger query?

Use conditional aggregation: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), or in PostgreSQL COUNT(*) FILTER (WHERE status = 'paid').

GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
COUNT(DISTINCT)COUNT(DISTINCT column) counts how many different values a column holds, ignoring NULL. Unique customers, distinct values per group, distinct pairs of columns.
HAVINGHAVING filters the groups made by GROUP BY using aggregate conditions such as COUNT(*) > 1 or SUM(amount) > 1000. Examples, HAVING vs WHERE, both together.
SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.