Command guide

intermediate date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Group Rows By Month

Bucket dates into month starts, then aggregate counts or totals per month.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Count orders and total revenue per month

March has two orders worth 100 total. April has three orders worth 150 total. The query first maps each order_date to the first day of its month, then aggregates on that derived month value.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, order_date DATE, amount INT);

INSERT INTO
  orders (id, order_date, amount)
VALUES
  (1, '2024-03-03', 40),
  (2, '2024-03-18', 60),
  (3, '2024-04-02', 75),
  (4, '2024-04-25', 25),
  (5, '2024-04-30', 50);

Validated query

SQL

SELECT
  DATE_FORMAT (order_date, '%Y-%m-01') AS month_start,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_amount
FROM
  orders
GROUP BY
  DATE_FORMAT (order_date, '%Y-%m-01')
ORDER BY
  month_start;

Expected result

month_start	total_orders	total_amount
2024-03-01	2	100
2024-04-01	3	150

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, order_date DATE, amount INT);

INSERT INTO
  orders (id, order_date, amount)
VALUES
  (1, '2024-03-03', 40),
  (2, '2024-03-18', 60),
  (3, '2024-04-02', 75),
  (4, '2024-04-25', 25),
  (5, '2024-04-30', 50);

Validated query

SQL

SELECT
  CONVERT(
    VARCHAR(10),
    DATEADD (MONTH, DATEDIFF (MONTH, 0, order_date), 0),
    23
  ) AS month_start,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_amount
FROM
  orders
GROUP BY
  DATEADD (MONTH, DATEDIFF (MONTH, 0, order_date), 0)
ORDER BY
  month_start;

Expected result

month_start	total_orders	total_amount
2024-03-01	2	100
2024-04-01	3	150

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, order_date DATE, amount INT);

INSERT INTO
  orders (id, order_date, amount)
VALUES
  (1, '2024-03-03', 40),
  (2, '2024-03-18', 60),
  (3, '2024-04-02', 75),
  (4, '2024-04-25', 25),
  (5, '2024-04-30', 50);

Validated query

SQL

SELECT TO_CHAR(DATE_TRUNC('month', order_date::TIMESTAMP), 'YYYY-MM-DD') AS month_start, COUNT(*) AS total_orders, SUM(amount) AS total_amount FROM orders GROUP BY DATE_TRUNC('month', order_date::TIMESTAMP) ORDER BY month_start;

Expected result

month_start	total_orders	total_amount
2024-03-01	2	100
2024-04-01	3	150

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, order_date DATE, amount INT);

INSERT INTO
  orders (id, order_date, amount)
VALUES
  (1, '2024-03-03', 40),
  (2, '2024-03-18', 60),
  (3, '2024-04-02', 75),
  (4, '2024-04-25', 25),
  (5, '2024-04-30', 50);

Validated query

SQL

SELECT
  strftime ('%Y-%m-01', order_date) AS month_start,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_amount
FROM
  orders
GROUP BY
  strftime ('%Y-%m-01', order_date)
ORDER BY
  month_start;

Expected result

month_start	total_orders	total_amount
2024-03-01	2	100
2024-04-01	3	150

The month-bucketing function differs by engine, but the grouped monthly totals are identical.

Useful when

Where this command helps.

building monthly order or revenue summaries
grouping event data into reporting periods before charting

Explanation

What the command is doing.

Monthly reporting usually needs two steps: convert each date to its month bucket, then aggregate on that bucket. The exact truncation function differs by engine, but the pattern is the same. Once each row maps to the first day of its month, GROUP BY can count rows, sum amounts, or compute other aggregates per month.

Group Rows By Month

Count orders and total revenue per month

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.