Grouping the table’s data help’s us count and analyze the identical data and remove the redundancy. In PostgreSQL, users may encounter a situation where they need to group the table’s data based on a specific date field such as a month, year, etc. For this purpose, various built-in date functions can be utilized with the Postgres’ GROUP BY clause.

This blog will illustrate how to group the table’s data based on a year.

How to Group Data by Year in Postgres?

To group data by year, use the DATE_PART() function with the GROUP BY clause and pass “year” as an argument to the stated function. Consider the following syntax for a profound understanding:

SELECT DATE_PART('Year', date_val),
COUNT(col_name)
FROM tab_name
GROUP BY DATE_PART('Year', date_val);

In the above syntax, the DATE_PART() function will fetch the year from the given date, and the GROUP BY clause will group the data based on the extracted year.

Example: How to Group Table’s Data by Years in PostgreSQL?

We have created an “emp_info” table with the following data:

SELECT * FROM emp_info
ORDER BY emp_id;

Suppose we want to group the employees with respect to their joining year. For this purpose, we will utilize the DATE_PART() function along with the GROUP BY clause as follows:

SELECT
DATE_PART('Year', joining_date) AS joining_year,
COUNT(emp_id) AS total_employees
FROM emp_info
GROUP BY DATE_PART('Year', joining_date);

In the above query:

- The DATE_PART() function pulls the year from the “joining_date” column.
- The COUNT() function will calculate the number of employees in each group.
- Finally, the GROUP BY clause will group the employees according to the extracted year.

The output shows that three employees joined the company in “2023”, two in “2018”, and one in “2019” and “2021”.

Note: In PostgreSQL, the EXTRACT() and DATE_TRUNC() functions can also be used to group the table’s data based on a specific year.

Conclusion

In PostgreSQL, various inbuilt functions like DATE_PART(), EXTRACT(), and DATE_TRUNC() are used with the GROUP BY clause to group the table’s data by a specific date field. “Year” must be passed as an argument to the stated functions to group the table’s data based on a year. This post has explained how to group the table’s data by year in PostgreSQL.