Why is an unused budget shown as a missing balance?

Prepare the diagnostic input

Every budget should have a remaining amount under the fixture’s complete expense register. SUM of no matched amount is NULL, and subtracting it also returns NULL. COALESCE belongs around the aggregate so an unused budget keeps its full value.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE budgets(code TEXT PRIMARY KEY,budget_cents INTEGER);
CREATE TABLE expenses(id TEXT PRIMARY KEY,code TEXT,amount_cents INTEGER);
INSERT INTO budgets VALUES ('IT',100000),('OPS',200000);
INSERT INTO expenses VALUES ('X1','IT',20000);

Reproduce the misleading result and correct it

SELECT b.code,b.budget_cents-SUM(e.amount_cents) AS remaining_cents
FROM budgets b LEFT JOIN expenses e ON e.code=b.code GROUP BY b.code,b.budget_cents ORDER BY b.code;

SELECT b.code,b.budget_cents-COALESCE(SUM(e.amount_cents),0) AS remaining_cents
FROM budgets b LEFT JOIN expenses e ON e.code=b.code GROUP BY b.code,b.budget_cents ORDER BY b.code;

IT has HK$200 spending against HK$1,000, leaving HK$800. OPS has no expense and should retain HK$2,000, not an unknown balance. The fix addresses absence of expenditure; it does not prove the expense register is complete.

Check the boundary before applying the fix

Insert a real zero-value OPS expense in the fixture. The remaining budget still equals HK$2,000. Zero spending and no spending have the same arithmetic but different audit histories.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO expenses VALUES ('X2','OPS',0);

A matched expense with NULL amount can also be ignored by SUM. Flag such rows separately rather than using this query as completeness certification.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.