Quick Summary
In MySQL you'll want to group by two fields YEAR(date), MONTH(date):
SELECT
YEAR(order_time),
MONTH(order_time),
COUNT(order_id) AS total_orders
FROM orders
GROUP BY 1, 2In PostgreSQL use the date_trunc('month', timestamp) function.
SELECT
date_trunc('month', order_time)::date AS order_month,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY date_trunc('month', order_time)Let's begin with a simple Orders table that looks like this:
order_id | customer_id | order_time | order_price
----------+-------------+---------------------+-------------
1 | 2 | 2020-01-10 11:37:00 | 19.01
2 | 1 | 2021-01-10 12:43:00 | 27.43
3 | 2 | 2021-01-21 09:20:00 | 17.23
4 | 1 | 2021-02-03 09:38:00 | 17.23
5 | 7 | 2021-03-21 10:12:00 | 27.43
6 | 1 | 2021-04-01 08:45:00 | 19.01We'd like to group by month in queries in order to answer questions such as:
- What is the total monthly revenue for every month in this year?
- What month has seen the highest/lowest number of orders?
- What is the average number of orders per each calendar month in the past 5 years?
Before jumping to examples, let's first create the table and fill it with some example data.
Create example table - MySQL/PostgreSQL
CREATE TABLE orders (
order_id INTEGER,
customer_id INTEGER,
order_time TIMESTAMP,
order_price FLOAT
);
INSERT INTO orders VALUES
(1, 2, '2020-01-10 11:37', 19.01),
(2, 1, '2021-01-10 12:43', 27.43),
(3, 2, '2021-01-21 09:20', 17.23),
(4, 1, '2021-02-03 09:38', 17.23),
(5, 7, '2021-03-21 10:12', 27.43),
(6, 1, '2021-04-01 08:45', 19.01)
;
Group by month - total orders and total order price
MySQL
In MySQL, there are also several ways to group records by calendar month. Here we'll present a method that's probably the cleanest and the easiest to remember. We'll group by two fields - year and month. We'll use the YEAR() and MONTH() functions that extract the year/month from a date or timestamp field.
SELECT YEAR('2001-02-16 20:38:40') → 2001
SELECT MONTH('2001-02-16 20:38:40') → 2
Thus, for our problem - get total sales by month - we'll do the following query in MySQL:
SELECT
YEAR(order_time),
MONTH(order_time),
COUNT(order_id) AS total_orders,
SUM(order_price) AS total_order_price
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2
-----
| YEAR(order_time) | MONTH(order_time) | total_orders | total_order_price |
|------------------|-------------------|--------------|--------------------|
| 2020 | 1 | 1 | 19 |
| 2021 | 1 | 2 | 44.630001068115234 |
| 2021 | 2 | 1 | 17.200000762939453 |
| 2021 | 3 | 1 | 27.43000030517578 |
| 2021 | 4 | 1 | 19 |
Of course, we can do better on the output formatting, so let's fix that. We'll first get the full month name using MONTHNAME() and then use CONCAT() to merge year and month name into a single output field.
SELECT
CONCAT(MONTHNAME(order_time), ',', YEAR(order_time)) AS order_month,
COUNT(order_id) AS total_orders,
SUM(order_price) AS total_order_price
FROM orders
GROUP BY YEAR(order_time), MONTH(order_time)
ORDER BY YEAR(order_time), MONTH(order_time)
------
| order_month | total_orders | total_order_price |
|---------------|--------------|--------------------|
| January,2020 | 1 | 19 |
| January,2021 | 2 | 44.630001068115234 |
| February,2021 | 1 | 17.200000762939453 |
| March,2021 | 1 | 27.43000030517578 |
| April,2021 | 1 | 19 |
PostgreSQL
In order to group our orders by month, in PostgreSQL we'll use the date_trunc built-in function. date_trunc will truncate a date or timestamp to the specified date/time part. It's best explained by example:
date_trunc('hour',TIMESTAMP '2001-02-16 20:38:40') → 2001-02-16 20:00:00
date_trunc('month',TIMESTAMP '2001-02-16 20:38:40') → 2001-02-01 00:00:00
date_trunc('year', TIMESTAMP '2001-02-16 20:38:40') → 2001-01-01 00:00:00
So for our problem, to get the actual month of the order we'll simply group by
date_trunc('month', order_time):
SELECT
date_trunc('month', order_time) AS order_month,
COUNT(order_id) AS total_orders,
SUM(order_price) AS total_order_price
FROM orders
GROUP BY date_trunc('month', order_time)
ORDER BY date_trunc('month', order_time)
;
------
| order_month | total_orders | total_order_price |
|----------------------|--------------|--------------------|
| 2020-01-01T00:00:00Z | 1 | 19 |
| 2021-01-01T00:00:00Z | 2 | 44.62 |
| 2021-02-01T00:00:00Z | 1 | 17.2 |
| 2021-03-01T00:00:00Z | 1 | 27.43 |
| 2021-04-01T00:00:00Z | 1 | 19 |Notice how our total_orders and total_order_price are grouped by month in the output table.
However, we might want to display the month a bit more nicely, without the redundant time part.
All we need to know is that date_part() returns a timestamp value that we can either cast to date or format the output to our liking. Let's do both.
- Cast to date: At line
#2of our example, if we saydate_trunc('month', order_time)::date(notice the::date) we'll get the following output:
SELECT
date_trunc('month', order_time)::date AS order_month,
COUNT(order_id) AS total_orders,
SUM(order_price) AS total_order_price
FROM orders
GROUP BY date_trunc('month', order_time)
ORDER BY date_trunc('month', order_time)
------
| order_month | total_orders | total_order_price |
|-------------|--------------|--------------------|
| 2020-01-01 | 1 | 19 |
| 2021-01-01 | 2 | 44.629999999999995 |
| 2021-02-01 | 1 | 17.2 |
| 2021-03-01 | 1 | 27.43 |
| 2021-04-01 | 1 | 19 |2. Format the output: We can also use to_char function to control the output format for dates and timestamps. We could say to_char(date_trunc('month', order_time), 'YYYY, Month') to get the following output:
SELECT
to_char(date_trunc('month', order_time), 'YYYY, Month') AS order_month,
COUNT(order_id) AS total_orders,
SUM(order_price) AS total_order_price
FROM orders
GROUP BY date_trunc('month', order_time)
ORDER BY date_trunc('month', order_time)
------
| order_month | total_orders | total_order_price |
|-----------------|--------------|--------------------|
| 2020, January | 1 | 19 |
| 2021, January | 2 | 44.629999999999995 |
| 2021, February | 1 | 17.2 |
| 2021, March | 1 | 27.43 |
| 2021, April | 1 | 19 |