How to GROUP BY Only the Year in SQLite?

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

strftime('%Y', 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 from the date_posted column, you would use strftime() in the following way:

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

This would result in the following:

+-------------+------+
| total_posts | year |
+-------------+------+
|      2      | 2021 |
|      1      | 2020 |
|      1      | 2015 |
+-------------+------+

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');