SQL: COUNT(email) = 1 but COUNT(*) = 3?!PRO

COUNT(column) skips NULLs, COUNT(*) counts rows. Same table, two different answers.

-- SQL: COUNT(email) = 1 but COUNT(*) = 3 ?!
-- COUNT(column) skips NULLs. COUNT(*) counts rows.
CREATE TABLE users (name TEXT, email TEXT);
INSERT INTO users VALUES ('Asha', 'asha@mail.com'), ('Ben', NULL), ('Chen', NULL);
SELECT COUNT(email) FROM users;              -- 1 ?!
SELECT COUNT(*) FROM users;                  -- 3 ?!

-- Fix: say which one you mean
SELECT COUNT(*) AS users,
       COUNT(email) AS with_email,
       COUNT(*) - COUNT(email) AS no_email
FROM users;                                  -- 3 | 1 | 2 ✓

No app yet? Install it, then open the app and type the code S4 in Code Library.