COUNT and NULL Values

SQLNULL handlingMidSeniorUpdated Nov 10, 2026

Return a single row with two columns: total_users (every row) and users_with_phone (rows where phone is set).

Schema

CREATE TABLE users (
  id           BIGINT PRIMARY KEY,
  email        TEXT NOT NULL,
  phone        TEXT          -- nullable
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (c)

SELECT COUNT(*) AS total_users,
       COUNT(phone) AS users_with_phone
FROM users;

COUNT(*) counts rows including NULLs, while COUNT(col) skips rows where col IS NULL, that is exactly the distinction we need. (a) counts every row twice. (b) is correct in practice but only because id is NOT NULL; COUNT(*) is the clearer intent. (d) tries to SUM a text column, which errors.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT COUNT(*) AS total_users,
       COUNT(*) AS users_with_phone
FROM users;

Option (b)

SELECT COUNT(id) AS total_users,
       COUNT(phone) AS users_with_phone
FROM users;

Option (d)

SELECT COUNT(*) AS total_users,
       SUM(phone) AS users_with_phone
FROM users;