SQL COUNT(): Count Rows, Values and Unique Values

"How many?" is one of the most common questions you will ask a database. How many customers do we have? How many orders this month? How many employees in each department? COUNT answers all of them.

The example table#

Count all rows#

SELECT COUNT(*) AS employees
FROM employees;

employees
---------
5

COUNT is an aggregate function: it takes many rows and returns one value.

Count rows that match a condition#

WHERE filters first, then COUNT counts what is left:

SELECT COUNT(*) AS engineers
FROM employees
WHERE department = 'Engineering';

engineers
---------
2

Three kinds of COUNT#

SELECT COUNT(*)                   AS all_rows,
       COUNT(manager_id)          AS with_manager,
       COUNT(DISTINCT department) AS departments
FROM employees;

all_rows | with_manager | departments
---------+--------------+------------
5        | 3            | 3

Meera and Priya have no manager, so COUNT(manager_id) is 3, not 5. This difference matters a lot after a LEFT JOIN, where unmatched rows contain NULLs.

Count per group#

Combine COUNT with GROUP BY to count each group separately:

SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
ORDER BY employees DESC, department;

department  | employees
------------+----------
Engineering | 2
Sales       | 2
Marketing   | 1

COUNT never returns NULL#

When no rows match, COUNT returns 0, unlike SUM and AVG, which return NULL:

SELECT COUNT(*) AS big_orders
FROM orders
WHERE amount > 1000;

big_orders
----------
0

Counting with a condition inside#

Sometimes you want several counts in one row. A CASE expression inside SUM (or COUNT) does it:

SELECT COUNT(*) AS total,
       SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) AS in_sales
FROM employees;

This "conditional aggregation" pattern is covered in CASE. PostgreSQL also supports the neat COUNT(*) FILTER (WHERE department = 'Sales').

Common mistakes#

Practice#

SELECT COUNT(*)                 AS products,
       COUNT(DISTINCT category) AS categories
FROM products;

products | categories
---------+-----------
6        | 3

Key takeaway#