COUNT Function
SQL · Aggregate Functions
Theory
COUNT is an aggregate function: it reads many rows and returns a single number. It has three forms. COUNT(*) counts every row, even a row whose columns are all NULL. COUNT(column) counts only the rows where that column is not NULL. COUNT(DISTINCT column) counts how many different non-NULL values the column holds.
Suppose employees has 5 rows and Meera has no city recorded. COUNT(*) returns 5, COUNT(city) returns 4, and COUNT(DISTINCT dept) returns 3 because there are three departments (IT, HR, Sales). Put COUNT next to GROUP BY and it runs once per group, so you get a headcount per department instead of one total. Unlike SUM or AVG, COUNT never returns NULL: on an empty table or empty group it returns 0.
Why we use it
Almost every report starts with 'how many?': how many orders today, how many customers per city, how many rows failed a check. COUNT answers this directly in the database, so you do not have to pull every row into Excel or Python just to count it.
When to use it
Use COUNT(*) for 'how many rows', COUNT(col) for 'how many rows have this filled in', and COUNT(DISTINCT col) for 'how many unique values'. To check if any row exists at all, EXISTS is often faster than COUNT(*) > 0.
How it works
- Rows arrive after FROM and WHERE
- Is it COUNT(*)? Yes: Add 1 for every row, NULLs included. No: Look at the column value.
- Is the value NULL? Yes: Skip the row. No: Add 1 (with DISTINCT: only if not seen before).
- Repeat for every row in the group
- Return the count (0 if no rows)
Syntax
COUNT(column | *)Example
SELECT COUNT(*) FROM employees;Key points
- COUNT(*) counts all rows; COUNT(column) skips NULLs
- COUNT(DISTINCT col) counts unique non-NULL values
- Returns 0, never NULL, when there are no rows
- Often paired with GROUP BY to count per group
- Filter on a count with HAVING COUNT(*) > n, not WHERE