COUNT

COUNT is the fastest way to answer "how many?" questions.

The exact variant you choose changes what gets counted.

Core Concepts

flowchart TD
  A[Input rows] --> B{COUNT variant}
  B -->|COUNT(*)| C[Counts all rows]
  B -->|COUNT(column)| D[Counts non-NULL values]
  B -->|COUNT(DISTINCT column)| E[Counts unique non-NULL values]

Variant	Counts
COUNT(*)	Every row
COUNT(column)	Rows where column is not NULL
COUNT(DISTINCT column)	Unique non-null values

All vs Non-NULL

SELECT
  COUNT(*)      AS total_rows,
  COUNT(phone)  AS rows_with_phone
FROM contacts_count;

total_rows	rows_with_phone
4	2

Distinct Count

SELECT COUNT(DISTINCT city) AS unique_cities
FROM contacts_count;

unique_cities
2

Code Examples

-- Setup data with NULL and repeated values.
CREATE TABLE contacts_count (
  contact_id INTEGER PRIMARY KEY,
  city       TEXT,
  phone      TEXT
);

INSERT INTO contacts_count (city, phone)
VALUES
  ('Tokyo', '111-111'),
  ('Tokyo', NULL),
  ('Berlin', '222-222'),
  ('Berlin', NULL);

Expected output
Table created and four rows inserted.

-- Count rows per city.
SELECT city, COUNT(*) AS row_count
FROM contacts_count
GROUP BY city
ORDER BY city;

city	row_count
Berlin	2
Tokyo	2

SQLite-Specific Nuances

SQLite Nuance

COUNT(*) in SQLite counts rows regardless of column values, including rows with many NULL columns.

Common Pitfalls / Best Practices

Pitfall

Using COUNT(column) when you intended total row count, then getting lower numbers because of nulls.

Best Practice

Use COUNT(*) for total rows and COUNT(column) only when null-exclusion is intentional.

Quick Challenge

Count how many rows in contacts_count have missing phone numbers.

View Solution

SELECT COUNT(*) AS missing_phone_count
FROM contacts_count
WHERE phone IS NULL;

missing_phone_count
2