Why are two years of October sales merged?

Prepare the diagnostic input

The reader needs a timeline of distinct calendar periods. A month number is a category from one to twelve, not a unique reporting period. Preserve year in the grouping key; putting the year only in a displayed heading cannot separate values already merged by SQL.

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 sales(id TEXT PRIMARY KEY,sold_on TEXT,amount_cents INTEGER);
INSERT INTO sales VALUES ('S1','2025-10-01',10000),('S2','2026-10-02',20000);

Reproduce the misleading result and correct it

SELECT strftime('%m',sold_on) AS period,SUM(amount_cents) AS sales_cents FROM sales GROUP BY period ORDER BY period;

SELECT strftime('%Y-%m',sold_on) AS period,SUM(amount_cents) AS sales_cents FROM sales GROUP BY period ORDER BY period;

October 2025 has 10000 cents and October 2026 has 20000. The wrong grouping reports one 30000-cent October. The correction retains both periods, so later comparisons do not mistake combined history for growth in a single month.

Check the boundary before applying the fix

Add a sale on 31 October 2026. Only that year-month rises to 25000 cents; the previous year remains unchanged.

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 sales VALUES ('S3','2026-10-31',5000);

Dates are valid calendar dates and amounts share one currency. Fiscal periods, refunds and recognised revenue need separately defined rules.

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.