How do I group SQLite date values by calendar month?

Create a month key from each date, then group rows by that key and sum their amounts. Assuming sale_date contains valid, consistently formatted ISO date strings such as 2025-05-03, this query returns one row per month:

SELECT
  strftime('%Y-%m', sale_date) AS month,
  SUM(amount) AS monthly_total
FROM sales
GROUP BY strftime('%Y-%m', sale_date);

%Y-%m produces a year and month, so January 2025 is 2025-01. Including the year keeps January of different years in separate groups. SQLite’s strftime returns formatted text, and its date functions accept supported date/time values rather than every possible date string. [S1]

For illustration, suppose the table contains these rows:

sale_date   amount
2025-01-03  12
2025-01-20   8
2025-02-02  15
2025-02-28   5
2025-03-11   9

The query groups the two January rows together and the two February rows together. Its output would be:

month    monthly_total
2025-01  20
2025-02  20
2025-03   9

These values are a worked example, not a result from a real database.

First check the actual stored values and whether SQLite can interpret them as dates. SQLite has no dedicated date/time datatype: values may be stored as ISO-8601 text, Julian day numbers, or Unix timestamps. Its date functions support enumerated formats, not every ISO-8601 variation. [S1] Don’t assume a differently formatted string will yield the intended month key.

For a quick inspection, select a few stored dates alongside the expression:

SELECT sale_date, strftime('%Y-%m', sale_date) AS month
FROM sales
LIMIT 20;

Compare each displayed key with the calendar month you expect. If a value does not produce the expected key, confirm how it is stored and whether that representation is accepted by SQLite’s date functions before relying on the aggregate.

Check the month key for representative rows, then compare the monthly total with the amounts in those rows. For example, for 2025-01, inspect the sales whose dates produce that key and add their amount values. This helps distinguish a grouping issue from unexpected source rows or amounts. The date-function guidance establishes which kinds of date values SQLite can recognize, but it cannot verify your table’s contents or what amount means. [S1]