Why it happens
In SQL, COUNT(expr) counts only rows where expr IS NOT NULL, whereas COUNT(*) counts all rows in the group regardless of NULL values.
Because rows 2 and 3 have referrer = NULL, COUNT(referrer) skips them and only counts the one non-null row in each plan (1 for free, 1 for pro).
---
The Fix
Use COUNT(*) (or COUNT(id) on the non-null primary key) to count every signup row in each plan:
SELECT plan, COUNT(*) AS signups
FROM signups
GROUP BY plan
ORDER BY plan;
If you want both total signups and attributed signups side by side:
SELECT
plan,
COUNT(*) AS total_signups,
COUNT(referrer) AS with_referrer
FROM signups
GROUP BY plan
ORDER BY plan;🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: free 2, pro 2 (four signups in all).
SQL block 1 (SELECT plan, COUNT(*) AS signups FROM signups): ✅ matches the expected result
| plan | signups |
|---|---|
| free | 2 |
| pro | 2 |
SQL block 2 (SELECT plan,): ✅ matches the expected result
| plan | total_signups | with_referrer |
|---|---|---|
| free | 2 | 1 |
| pro | 2 | 1 |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.