Why does a quoted number fail against an integer SUM?

Prepare the report input

Original query seeks total quantity four. A declared INTEGER column has affinity, but SUM(qty) is an expression without that column affinity. Comparing it with text literal '4' does not perform the same conversion as a direct integer-column comparison. Use numeric 4.

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 units(id INTEGER PRIMARY KEY,group_code TEXT NOT NULL,qty INTEGER NOT NULL); INSERT INTO units VALUES(1,'A',2),(2,'A',2),(3,'B',4);

Compare misleading and corrected results

SELECT group_code,SUM(qty) AS total_units FROM units GROUP BY group_code HAVING SUM(qty)='4' ORDER BY group_code;

SELECT group_code,SUM(qty) AS total_units FROM units GROUP BY group_code HAVING SUM(qty)=4 ORDER BY group_code;

Bad result is empty although A=2+2 and B=4. Numeric comparison returns both groups. Quoting a numeric filter can change meaning after an aggregation step.

Check a fresh boundary case

Insert text '4' into the INTEGER qty column for C. Column affinity stores it numerically; the numeric aggregate filter also returns C.

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 units VALUES(4,'C','4');
SELECT group_code,SUM(qty) AS total_units FROM units GROUP BY group_code HAVING SUM(qty)=4 ORDER BY group_code;