Forgly

SQL to group by year

Group rows by year in SQL — extract the year from a date or timestamp to count or sum records per calendar year.

PostgreSQL
SELECT EXTRACT(YEAR FROM created_at) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY EXTRACT(YEAR FROM created_at)
ORDER BY year;
MySQL
SELECT YEAR(created_at) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY YEAR(created_at)
ORDER BY year;
SQL Server
SELECT YEAR(created_at) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY YEAR(created_at)
ORDER BY year;
SQLite
SELECT strftime('%Y', created_at) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY strftime('%Y', created_at)
ORDER BY year;

How to group records by year

To group by year in SQL, extract the year from your date column and GROUP BY it. Copy-paste examples below for PostgreSQL (EXTRACT), MySQL and SQL Server (YEAR()), and SQLite (strftime) — each ready to run.

How it works

  • Extracting only the year collapses every row from the same calendar year into one bucket.
  • GROUP BY the same expression you SELECT — most engines reject grouping by the column alias.
  • To break a year down further, group by the year expression and the month expression together (year, then month).

Tip

EXTRACT(YEAR ...) and YEAR() return a number, so 2024 sorts correctly; use DATE_TRUNC('year', created_at) in Postgres instead if you want a real date (the first day of each year).

Related SQL queries

Frequently asked questions

How do I group records by year in SQL?

To group by year in SQL, extract the year from your date column and GROUP BY it. Copy-paste examples below for PostgreSQL (EXTRACT), MySQL and SQL Server (YEAR()), and SQLite (strftime) — each ready to run. Extracting only the year collapses every row from the same calendar year into one bucket. GROUP BY the same expression you SELECT — most engines reject grouping by the column alias. To break a year down further, group by the year expression and the month expression together (year, then month).

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.