aggregate

COUNT()

Returns the number of rows that match a query, optionally counting only non-null values in a specific column.

Description

COUNT is an aggregate function that tells you how many rows are in a result set. COUNT(*) counts every row, no matter what's in it, including rows with NULL values. COUNT(column_name) counts only the rows where that column has a non-NULL value, which makes it useful for spotting missing data.

Syntax

COUNT(*)
COUNT(column_name)
COUNT(DISTINCT column_name)

Parameters

Name	Description	Optional
expression	A column name, or `*` to count all rows. Add `DISTINCT` before the column name to count only unique values.	Yes (defaults to `*` in typical usage)

Return Type

COUNT always returns a BIGINT, an integer count. It never returns NULL, even if the table is empty, in which case it returns 0.

Examples

Employees & Departments

Loading database engine...

Employees & Departments

Loading database engine...

COUNT(column) vs COUNT(*)

COUNT(*) and COUNT(column_name) can give different results. If the employees.manager_id column has NULL values (for employees with no manager), COUNT(manager_id) will be lower than COUNT(*) because NULLs are skipped.

Common Mistakes

Assuming COUNT(column_name) counts all rows. It only counts non-NULL values in that column, which can silently undercount rows if you meant to count everything.
Forgetting GROUP BY when counting per category. SELECT department_id, COUNT(*) FROM employees without a GROUP BY clause will either error or collapse into a single row, not give you per-department counts.
Using COUNT(DISTINCT *). DISTINCT must be applied to a specific column or expression, not the * wildcard.