Group by Year, Month, or Day in MySQL

Previously, I wrote an article on how to group records by year, month, and day in PostgreSQL — if you haven’t seen it yet, you can check it out here.

In this follow-up, we’ll explore how to achieve the same results in MySQL. Using a sample orders table, we’ll go through different ways to group data by year, month, and day using functions like DATE_FORMAT(), EXTRACT(), YEAR(), MONTH(), DAY(), and more.

Whether you’re building reports, dashboards, or just exploring your data, these techniques will help you slice it by time with ease.

Before digging deeper, let’s consider that you have the following orders table:

And your manager asks you three requirements:

1. Calculate the total sales every year.
2. Calculate the total sales every month in 2023.
3. Calculate the total sales every day in July 2023.

To calculate the total sales per year:

1. Using DATE_FORMAT()

SELECT DATE_FORMAT(createdAt, '%Y') AS year, SUM(price) AS total
FROM orders
GROUP BY year
ORDER BY year;

This statement returns:

2. Using EXTRACT()

SELECT EXTRACT(YEAR FROM createdAt) AS year, SUM(price) AS total
FROM orders
GROUP BY year
ORDER BY year;

And it returns the same result.

3. Using YEAR()

SELECT YEAR(createdAt) AS year, SUM(price) AS total
FROM orders
GROUP BY year
ORDER BY year;

Also, returns the same output.

To calculate the total sales per month in 2023:

1. Using DATE_FORMAT()

SELECT DATE_FORMAT(createdAt, '%Y-%m') AS `year_month`, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023
GROUP BY `year_month`
ORDER BY `year_month`;

This query returns:

2. Using EXTRACT()

SELECT EXTRACT(MONTH FROM createdAt) AS month, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023
GROUP BY month
ORDER BY month;

This query returns:

3. Using MONTH()

SELECT MONTH(createdAt) AS month, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023
GROUP BY month
ORDER BY month;

And this returns the same previous result.

To calculate the total sales per day in July 2023:

1. Using DATE_FORMAT()

SELECT DATE_FORMAT(createdAt, '%Y-%m-%d') AS date, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023 AND MONTH(createdAt) = 7
GROUP BY date
ORDER BY date;

This query returns:

2. Using DATE()

SELECT DATE(createdAt) AS day_date, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023 AND MONTH(createdAt) = 7
GROUP BY day_date
ORDER BY day_date;

The same result.

3. Using EXTRACT()

SELECT EXTRACT(DAY FROM createdAt) AS day, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023 AND MONTH(createdAt) = 7
GROUP BY day
ORDER BY day;

This query returns:

4. Using DAY()

SELECT DAY(createdAt) AS day, SUM(price) AS total
FROM orders
WHERE YEAR(createdAt) = 2023 AND MONTH(createdAt) = 7
GROUP BY day
ORDER BY day;

And this returns the same previous result.

In this article, we learned how to group data by a specific date interval, year, month, and day.

And to do so, we used some functions:

• DATE_FORMAT()
• EXTRACT()
• YEAR(), MONTH(), DAY(), and DATE()

If you enjoyed this article, I’d truly appreciate it if you could share it—it really motivates me to keep creating more helpful content!

If you’re interested in exploring more, check out these articles.

• MongoDB GridFS Made simple
• Isolation Levels In SQL Server With Examples
• 4 Ways To Handle Asynchronous JavaScript

Thanks for sticking with me until the end—I hope you found this article valuable and enjoyable!