Grouping by month

group-by-month.sql
-- PostgreSQL
SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- SQL Server 2022+
SELECT DATE_TRUNC(MONTH, order_date) AS order_month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC(MONTH, order_date);

-- SQL Server, pre-2022
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS order_month, SUM(amount) AS revenue
FROM orders
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS order_month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-01');
Advertisement

Grouping by week (and the week-start trap)

group-by-week.sql
-- PostgreSQL: DATE_TRUNC('week', ...) always starts weeks on Monday, regardless of locale
SELECT DATE_TRUNC('week', order_date) AS week_start, SUM(amount) AS revenue
FROM orders
GROUP BY 1;

-- SQL Server: week start depends on the session's DATEFIRST setting (often Sunday = 7 by default)
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, order_date) - 1), CAST(order_date AS DATE)) AS week_start,
       SUM(amount) AS revenue
FROM orders
GROUP BY 1;
PostgreSQL's DATE_TRUNC('week', ...) always uses Monday as day one, independent of any locale setting. SQL Server's week-based functions follow @@DATEFIRST, which defaults to 7 (Sunday) in US English server settings but varies by locale. The same dataset, truncated by week in each database with default settings, can bucket a Sunday order into a different week entirely — always confirm which day a "week" starts on before comparing weekly totals across systems.
Advertisement

Grouping by quarter

group-by-quarter.sql
-- PostgreSQL
SELECT DATE_TRUNC('quarter', order_date) AS quarter_start, SUM(amount) AS revenue
FROM orders
GROUP BY 1;

-- SQL Server / MySQL: combine year and quarter number since neither returns a date directly
SELECT DATEPART(YEAR, order_date) AS yr, DATEPART(QUARTER, order_date) AS qtr, SUM(amount) AS revenue
FROM orders
GROUP BY DATEPART(YEAR, order_date), DATEPART(QUARTER, order_date);

A full monthly revenue report

monthly-report.sql
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(DISTINCT order_id) AS orders,
  SUM(amount)                    AS revenue,
  ROUND(AVG(amount), 2)           AS avg_order_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY 1
ORDER BY 1;

Referencing the GROUP BY column by its position (GROUP BY 1) instead of repeating the full DATE_TRUNC(...) expression is a common readability shortcut supported by PostgreSQL, MySQL, and SQL Server alike — useful once the truncation expression gets long.

Common mistakes

  • Comparing weekly totals across two databases without checking each one's week-start convention — the same data can produce genuinely different weekly buckets.
  • Truncating in SELECT but grouping by the raw date column, causing either an error or one row per raw timestamp instead of one per period.
  • Assuming SQL Server has DATE_TRUNC on any version — it was only added in SQL Server 2022; earlier versions need the DATEFROMPARTS or DATEADD-based workaround.
  • Forgetting a WHERE filter on the raw date column before truncating on a very large table, scanning far more rows than the report actually needs.

Key takeaways

  • PostgreSQL: DATE_TRUNC('month'/'week'/'quarter', column) handles all three directly.
  • SQL Server 2022+ has the same DATE_TRUNC; earlier versions need DATEFROMPARTS or DATEADD/DATEPART workarounds.
  • MySQL commonly uses DATE_FORMAT() for month-level grouping and QUARTER()/YEAR() for quarterly grouping.
  • Week-start day is the biggest cross-database trap — PostgreSQL always starts Monday; SQL Server follows @@DATEFIRST.
  • The GROUP BY clause must match the truncation expression (or its position/alias) used in SELECT.