Often in PostgreSQL you may want to group query results by month and year.
You can use the date_trunc statement with the following basic syntax to do so:
SELECT date_trunc('month', date_sold) AS month_year, SUM(amount) AS total
FROM products
GROUP BY month_year
ORDER BY month_year;
This particular example will calculate the sum of values in the amount column and group the results by the month and year in the date_sold column.
Note: The ORDER BY clause is optional but we include it so that the results are sorted in order by date.
The following example shows how to use this syntax in practice.
Example: How to Group Results by Month and Year in PostgreSQL
Suppose that we create the following table named products that contains information about the number of products sold on various dates at some company:
-- create table named 'products'
CREATE TABLE products (
productID INTEGER PRIMARY KEY,
date_sold DATE,
amount INTEGER
);
-- add rows to the table
INSERT INTO products VALUES (0001, '2023-10-11', 33);
INSERT INTO products VALUES (0002, '2024-08-12', 15);
INSERT INTO products VALUES (0003, '2024-08-10', 20);
INSERT INTO products VALUES (0004, '2024-09-15', 45);
INSERT INTO products VALUES (0005, '2024-09-18', 50);
INSERT INTO products VALUES (0006, '2024-09-22', 23);
INSERT INTO products VALUES (0007, '2024-10-14', 30);
INSERT INTO products VALUES (0008, '2024-10-30', 12);
-- view all rows in table
SELECT * FROM products;
productid | date_sold | amount
-----------+------------+--------
1 | 2023-10-11 | 33
2 | 2024-08-12 | 15
3 | 2024-08-10 | 20
4 | 2024-09-15 | 45
5 | 2024-09-18 | 50
6 | 2024-09-22 | 23
7 | 2024-10-14 | 30
8 | 2024-10-30 | 12
Suppose that we would like to calculate the sum of values in the amount column, grouped by month and year.
We can use the following syntax to do so:
SELECT date_trunc('month', date_sold) AS month_year, SUM(amount) AS total
FROM products
GROUP BY month_year
ORDER BY month_year;
month_year | total
------------------------+-------
2023-10-01 00:00:00+00 | 33
2024-08-01 00:00:00+00 | 35
2024-09-01 00:00:00+00 | 118
2024-10-01 00:00:00+00 | 42
The resulting table shows the sum of values from the amount column in the original table, grouped by month and year.
For example, from the output we can see:
- A total of 33 sales were made in 2023-10.
- A total of 35 sales were made in 2024-08.
- A total of 118 sales were made in 2024-09.
- A total of 42 sales were made in 2024-10.
Feel free to use similar syntax to group the results of your own query by month and year.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: How to Extract Hours and Minutes from Timestamp
