MySQL GROUP BY Date: Per Day, Month, Year

You can group by an expression, not only by a column. The most common case is dates: per day, per month, per year.

query.sql

SQL

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sold_at DATETIME NOT NULL,
    amount DECIMAL(8,2) NOT NULL,
    channel VARCHAR(10) NOT NULL
);
INSERT INTO sales (sold_at, amount, channel) VALUES
    ('2026-05-03 10:15:00', 100, 'web'),  ('2026-05-03 16:40:00', 50,  'store'),
    ('2026-05-20 09:00:00', 250, 'web'),  ('2026-06-11 13:30:00', 80,  'store'),
    ('2026-06-11 18:05:00', 120, 'web'),  ('2026-06-30 23:59:00', 60,  'web'),
    ('2026-07-01 00:00:00', 300, 'store'), ('2027-01-05 11:00:00', 90, 'web');

Per day

query.sql

SQL

SELECT DATE(sold_at) AS day, COUNT(*) AS sales, SUM(amount) AS revenue
FROM sales
GROUP BY DATE(sold_at)
ORDER BY day;

Output

OUTPUT

+------------+-------+---------+
| day        | sales | revenue |
+------------+-------+---------+
| 2026-05-03 |     2 |  150.00 |
| 2026-05-20 |     1 |  250.00 |
| 2026-06-11 |     2 |  200.00 |
| 2026-06-30 |     1 |   60.00 |
| 2026-07-01 |     1 |  300.00 |
| 2027-01-05 |     1 |   90.00 |
+------------+-------+---------+
6 rows in set

Per month

query.sql

SQL

SELECT DATE_FORMAT(sold_at, '%Y-%m') AS month, SUM(amount) AS revenue
FROM sales
GROUP BY DATE_FORMAT(sold_at, '%Y-%m')
ORDER BY month;

Output

OUTPUT

+---------+---------+
| month   | revenue |
+---------+---------+
| 2026-05 |  400.00 |
| 2026-06 |  260.00 |
| 2026-07 |  300.00 |
| 2027-01 |   90.00 |
+---------+---------+
4 rows in set

%Y-%m sorts correctly as text, with the year first. Alternatively YEAR(sold_at), MONTH(sold_at):

query.sql

SQL

SELECT YEAR(sold_at) AS year, MONTH(sold_at) AS month, SUM(amount) AS revenue
FROM sales
GROUP BY YEAR(sold_at), MONTH(sold_at)
ORDER BY year, month;

Output

OUTPUT

+------+-------+---------+
| year | month | revenue |
+------+-------+---------+
| 2026 |     5 |  400.00 |
| 2026 |     6 |  260.00 |
| 2026 |     7 |  300.00 |
| 2027 |     1 |   90.00 |
+------+-------+---------+
4 rows in set

Never group by MONTH(sold_at) alone when the data spans several years: May 2026 and May 2027 would merge.

Per week and per quarter

query.sql

SQL

SELECT YEARWEEK(sold_at, 3) AS iso_year_week, QUARTER(sold_at) AS quarter, COUNT(*) AS sales
FROM sales
GROUP BY YEARWEEK(sold_at, 3), QUARTER(sold_at)
ORDER BY iso_year_week;

Output

OUTPUT

+---------------+---------+-------+
| iso_year_week | quarter | sales |
+---------------+---------+-------+
|        202618 |       2 |     2 |
|        202621 |       2 |     1 |
|        202624 |       2 |     2 |
|        202627 |       2 |     1 |
|        202627 |       3 |     1 |
|        202701 |       1 |     1 |
+---------------+---------+-------+
6 rows in set

Using the alias in GROUP BY (MySQL extension)

MySQL lets you group by a SELECT alias, which shortens the query. Standard SQL does not allow it:

query.sql

SQL

SELECT DATE_FORMAT(sold_at, '%Y') AS year, COUNT(*) AS sales FROM sales GROUP BY year ORDER BY year;

Output

OUTPUT

+------+-------+
| year | sales |
+------+-------+
| 2026 |     7 |
| 2027 |     1 |
+------+-------+
2 rows in set

It works in MySQL, but may not run on other databases.

Bands with CASE

Group by a computed category:

query.sql

SQL

SELECT CASE WHEN amount < 75 THEN 'small'
            WHEN amount < 200 THEN 'medium'
            ELSE 'large' END AS size_band,
       COUNT(*) AS sales,
       SUM(amount) AS revenue
FROM sales
GROUP BY size_band
ORDER BY MIN(amount);

Output

OUTPUT

+-----------+-------+---------+
| size_band | sales | revenue |
+-----------+-------+---------+
| small     |     2 |  110.00 |
| medium    |     4 |  390.00 |
| large     |     2 |  550.00 |
+-----------+-------+---------+
3 rows in set

Sorting by MIN(amount) orders the bands from small to large.

Days with no data

A GROUP BY shows only the groups that exist. A day with no sales has no row. To list every day, generate the days and LEFT JOIN the sales:

query.sql

SQL

WITH RECURSIVE days AS (
    SELECT DATE('2026-06-09') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM days WHERE day < '2026-06-13'
)
SELECT d.day, COUNT(s.id) AS sales, COALESCE(SUM(s.amount), 0) AS revenue
FROM days d
LEFT JOIN sales s ON DATE(s.sold_at) = d.day
GROUP BY d.day
ORDER BY d.day;

Output

OUTPUT

+------------+-------+---------+
| day        | sales | revenue |
+------------+-------+---------+
| 2026-06-09 |     0 |    0.00 |
| 2026-06-10 |     0 |    0.00 |
| 2026-06-11 |     2 |  200.00 |
| 2026-06-12 |     0 |    0.00 |
| 2026-06-13 |     0 |    0.00 |
+------------+-------+---------+
5 rows in set

The recursive CTE creates the five days. The LEFT JOIN keeps days without sales, and COUNT(s.id) counts only real matches (Recursive CTEs). For long periods, keep a permanent calendar table.

Performance: the grouping index

GROUP BY DATE(sold_at) has to compute the date for every row. For a big table, reading an index on sold_at does not help with DATE() directly. Two practical options:

Add a generated column sold_on DATE AS (DATE(sold_at)) with an index, and group by it (Generated Columns).
Maintain a summary table (daily totals) that your application or a scheduled job updates, and report from that (Summary Tables).

For a report over a limited range, keep the range filter sargable: WHERE sold_at >= '2026-06-01' AND sold_at < '2026-07-01', and then group the few rows that remain.

warning

Mixing years

GROUP BY MONTH(date) combines the same month of different years. Group by year and month together, or by DATE_FORMAT(date, '%Y-%m').