Forgly

SQL to group by year

Summarize rows per calendar year from a date or timestamp column.

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

Grouping by year pulls the year out of each timestamp and aggregates on it. Every dialect has a direct way to extract the year.

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?

Grouping by year pulls the year out of each timestamp and aggregates on it. Every dialect has a direct way to extract the year. 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.