SQL Reference ยท Aggregate Functions
SQL COUNT() function
Reference for the SQL COUNT() aggregate: COUNT(*), COUNT(column) and COUNT(DISTINCT), how NULL is treated, empty tables, GROUP BY, FILTER and performance.
COUNT() is an aggregate function that returns how many rows or values a query found. It comes in three forms with different meanings: COUNT(*) counts rows, COUNT(column) counts rows where the column is not NULL, and COUNT(DISTINCT column) counts the different non-NULL values.
Without GROUP BY, an aggregate query returns exactly one row, even over an empty table. COUNT always returns a number and never NULL: zero when nothing matched.
SELECT COUNT(*) FROM customers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
COUNT(*) COUNT(expression) COUNT(DISTINCT expression) COUNT(...) FILTER (WHERE condition) -- standard; SQLite, PostgreSQL COUNT(...) OVER (PARTITION BY ...) -- as a window function
| Form | Counts |
|---|---|
COUNT(*) |
Every row in the group, NULLs included. |
COUNT(expression) |
Rows where the expression is not NULL. |
COUNT(DISTINCT expression) |
Different non-NULL values of the expression. |
Returns: an integer (BIGINT in many databases). Zero if there are no rows or only NULLs.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
COUNT(*), COUNT(col), COUNT(DISTINCT col) |
Yes | Yes | Yes | Yes | Yes |
| Result type | Integer | bigint |
bigint |
int (COUNT_BIG() for bigint) |
NUMBER |
FILTER (WHERE ...) |
Yes (3.30+) | Yes | No | No | No |
COUNT(DISTINCT a, b) (several columns) |
No | No | Yes | No | No |
Window use COUNT(*) OVER (...) |
Yes (3.25+) | Yes | Yes (8.0+) | Yes | Yes |
-- MySQL: distinct combinations of two columns SELECT COUNT(DISTINCT city, country) FROM customers; -- Portable version for any database SELECT COUNT(*) FROM (SELECT DISTINCT city, country FROM customers) AS combos; -- SQL Server: counts beyond about 2 billion rows SELECT COUNT_BIG(*) FROM big_table;
Usage notes
COUNT(*)versusCOUNT(column). The first counts rows; the second skipsNULLs. They differ whenever the column has missing values, which is a common source of wrong reports.COUNT(1)equalsCOUNT(*). There is no speed difference in modern databases.COUNT(*)is the idiom.- Counting inside outer joins.
COUNT(*)counts the unmatched row as 1;COUNT(right_table.id)counts it as 0. Use the column form when you want "how many matches". DISTINCTis slower because the database has to deduplicate. Only use it when duplicates are possible.- Conditional counts:
COUNT(CASE WHEN cond THEN 1 END)works everywhere becauseCASEreturnsNULLwhen nothing matches. TheFILTERclause is a tidier form where available: seeFILTER. - You cannot use
COUNTinWHERE. Filter on groups withHAVING, or compute the count in a subquery. - Speed: PostgreSQL and SQLite scan an index or the table for
COUNT(*). MySQL's InnoDB does too; only MyISAM stores an exact row count. On huge tables, keep a counter or use statistics for approximate counts. EXISTSis better thanCOUNT(*) > 0when you only need to know if any row exists.