SQLite count() Function

SQLite count() is an aggregate function that counts rows or non-NULL values. Choosing the right form matters: counting rows is not always the same as counting completed events, filled-in values, or distinct items.

Syntax

count(*)
count(expression)
count(DISTINCT expression)
count(*) FILTER (WHERE condition)

• count(*) counts all rows, including rows that contain NULL values.
• count(expression) counts rows where the expression is not NULL.
• count(DISTINCT expression) counts different, non-NULL values.
• FILTER (WHERE ...) counts only rows matching a condition. It can be used with aggregate functions, including count().

All forms return an SQL integer. With no qualifying rows, count() returns 0, not NULL.

Examples

COUNT(*) versus COUNT(column)

This query models visit records, some of which do not yet have a visit date:

WITH visits(person, visited_at) AS (
    VALUES
        ('Ana', '2026-01-04'),
        ('Ana', NULL),
        ('Ben', '2026-01-07'),
        ('Ben', '2026-01-07')
)
SELECT
    person,
    count(*) AS records,
    count(visited_at) AS dated_visits
FROM visits
GROUP BY person
ORDER BY person;

person  records  dated_visits
------  -------  ------------
Ana     2        1
Ben     2        2

Ana has two records but only one non-NULL visit date. Neither count is inherently incorrect: use count(*) for the number of records, and count(visited_at) for records that have a date.

Count unique values

WITH visits(person, visited_at) AS (
    VALUES
        ('Ana', '2026-01-04'),
        ('Ana', NULL),
        ('Ben', '2026-01-07'),
        ('Ben', '2026-01-07')
)
SELECT
    person,
    count(DISTINCT visited_at) AS unique_visit_dates
FROM visits
GROUP BY person
ORDER BY person;

person  unique_visit_dates
------  ------------------
Ana     1
Ben     1

Repeated non-NULL values count once; NULL values do not count.

Conditional counts with FILTER

You can count multiple conditions in one grouped query:

WITH orders(customer, status) AS (
    VALUES
        ('Ana', 'paid'),
        ('Ana', 'pending'),
        ('Ana', 'paid'),
        ('Ben', 'pending')
)
SELECT
    customer,
    count(*) AS all_orders,
    count(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders
GROUP BY customer
ORDER BY customer;

customer  all_orders  paid_orders
--------  ----------  -----------
Ana       3           2
Ben       1           0

FILTER is supported in SQLite 3.30.0 and later.

What happens with an empty result set?

SELECT
    count(*) AS row_count,
    count(x) AS non_null_count
FROM (SELECT 1 AS x WHERE 0);

row_count  non_null_count
---------  --------------
0          0

Both forms return zero, even though the query has no rows.

Frequently asked questions

Does COUNT(*) include NULL?

Yes. count(*) counts rows regardless of whether columns are NULL. count(column) excludes rows whose selected expression evaluates to NULL.

Can I count distinct combinations of columns?

count(DISTINCT a, b) is not supported in SQLite. If you need a count of unique pairs, select distinct pairs in a subquery, then apply count(*):

SELECT count(*) AS unique_pairs
FROM (SELECT DISTINCT a, b FROM your_table);

Try the examples online

Run and edit the self-contained queries above in SQLite Playground. The syntax and NULL behavior follow the official SQLite aggregate-function reference.