Why does filtering large rows produce the wrong total-qualified groups?

Prepare the report input

Original report lists groups with net signed unit total above five. A has +4,+4; B has +6,−2. WHERE units>5 removes input rows before grouping; HAVING SUM(units)>5 applies the intended group-level test.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE changes(id INTEGER PRIMARY KEY,group_code TEXT NOT NULL,units INTEGER NOT NULL); INSERT INTO changes VALUES(1,'A',4),(2,'A',4),(3,'B',6),(4,'B',-2);

Compare misleading and corrected results

SELECT group_code,SUM(units) AS total_units FROM changes WHERE units>5 GROUP BY group_code ORDER BY group_code;

SELECT group_code,SUM(units) AS total_units FROM changes GROUP BY group_code HAVING SUM(units)>5 ORDER BY group_code;

Bad output lists B=6 while losing A. True totals are A=8 and B=4, so only A qualifies. Each supplied input is already a signed delta; negative corrections belong in the total.

Check a fresh boundary case

Add +2 to B. Its net reaches six and it then qualifies alongside A.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO changes VALUES(5,'B',2);
SELECT group_code,SUM(units) AS total_units FROM changes GROUP BY group_code HAVING SUM(units)>5 ORDER BY group_code;