Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT COUNT(*) FROM customers;

Try it

SQL The three forms of COUNT ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL COUNT with GROUP BY, WHERE and a conditional count ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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(*) versus COUNT(column). The first counts rows; the second skips NULLs. They differ whenever the column has missing values, which is a common source of wrong reports.
  • COUNT(1) equals COUNT(*). 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".
  • DISTINCT is 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 because CASE returns NULL when nothing matches. The FILTER clause is a tidier form where available: see FILTER.
  • You cannot use COUNT in WHERE. Filter on groups with HAVING, 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.
  • EXISTS is better than COUNT(*) > 0 when you only need to know if any row exists.