How to GROUP BY Month and Year in SQLite?

You can GROUP BY the year and the month from a SQLite date by using the strftime() function with the %Y and %m format specifiers like so:

strftime('%Y-%m', date_col)

For example, consider the following table of blog posts:

+----+-----------------+---------------------+
| id | title           | date_posted         |
+----+-----------------+---------------------+
|  1 | Lorem Ipsum     | 2020-01-01 00:00:00 |
|  2 | Proin ut Ante   | 2021-08-01 00:00:00 |
|  3 | Curabitur nulla | 2021-08-01 00:00:00 |
|  4 | Fusce vitae     | 2015-12-01 00:00:00 |
+----+-----------------+---------------------+

To GROUP BY only the year and the month from the date_posted column, you would use strftime() in the following way:

SELECT COUNT(*) total_posts, strftime('%Y-%m', date_posted) year_month
FROM blog_post
GROUP BY year_month
ORDER BY year_month DESC

This would result in the following:

+-------------+------------+
| total_posts | year_month |
+-------------+------------+
|      2      |  2021-08   |
|      1      |  2020-01   |
|      1      |  2015-12   |
+-------------+------------+

If you would like to try this example yourself, then you can create the table and insert the data (as shown in the examples above), in the following way:

CREATE TABLE `blog_post` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(25) NOT NULL,
    date_posted DATETIME NOT NULL
);

INSERT INTO blog_post(`title`, `date_posted`) VALUES
('Lorem Ipsum', '2020-01-01 00:00:00'),
('Proin ut Ante', '2021-08-01 00:00:00'),
('Curabitur nulla', '2021-08-01 00:00:00'),
('Fusce vitae', '2015-12-01 00:00:00');