The COUNT(X) function only counts non-null values

I was running a tally in a photos table with a text column photos, and I got a surprising result:

sqlite> SELECT media, COUNT(media) FROM photos GROUP BY media;
|0
photo|1813253
video|1131

Why is there a value with 0 results in the tally? That seems odd!

The issue turned out to be a misunderstanding on my part about the COUNT(X) function – it only counts the number of times that X is not NULL.

I had a number of null rows in the database, but they weren’t being counted:

sqlite> SELECT COUNT(*) FROM photos WHERE media IS NULL;
340

The tally query I actually want uses COUNT(*), not COUNT(X):

sqlite> SELECT media, COUNT(*) FROM photos GROUP BY media;
|340
photo|1813253
video|1131

Other thoughts/clues

I initially asked ChatGPT, which told me that GROUP BY can find deleted rows. I haven’t been able to find an independent source for this, so I think it might be a hallucination.

It did suggest running the VACUUM command as a way to trigger some sort of database rebuild, but that didn’t change the behaviour I was seeing.
One of ChatGPT’s other suggestions was creating a new table – that felt like a more helpful suggestion, because it might force a table rebuild and flush any transient data.

I ended up running the following lines repeatedly, adjusting the limit/offset and trying to cut down the table to find the rows that triggered the problem:
```
DROP TABLE
  new_photos;

CREATE TABLE
  new_photos
AS SELECT
  id, media
FROM
  photos
LIMIT 5
OFFSET 54850;
    
SELECT media, COUNT(media) FROM new_photos GROUP BY media;
```
Once I’d got a small enough set, I inspected those rows manually and found the data that was causing the mysterious tally behaviour:
```
sqlite> SELECT id, media FROM photos LIMIT 5 OFFSET 54850;
14541672258|photo
14541680439|photo
14728030192|photo
14725963694|photo
52966470900|
```
This was the clue I needed to go double-check how COUNT(X) works.
The tally should count every row in the database. If I’d cross-checked the total of the tally with the number of rows in the database, I’d have noticed the missing rows:
```
sqlite> SELECT COUNT(*) FROM photos;
1814724
```
but the initial tally has 1813253 + 1131 = 1814384, which is short.