EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL group by month
 

PostgreSQL group by month

Updated May 26, 2023

PostgreSQL group by month

 

 

Introduction to PostgreSQL group by month

PostgreSQL group by month is used to retrieve the data by using a monthly basis, we have applied group by clause on date_trunc function to retrieve table data as per month basis in PostgreSQL. If we need table data on a per month basis then we have to use PostgreSQL group by month in PostgreSQL. We have used date_trunc, group by, and aggregate functions to retrieve table data on a monthly basis in PostgreSQL, we have used the date_trunc function on a column from which we have retrieved data on a monthly basis.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Below is the syntax of the group by month in PostgreSQL.

Group by month using date_trunc function

Select DATE_TRUNC ('month', name_of_column) count (name_of_column) from name_of_table GROUP BY DATE_TRUNC ('month', name_of_column);

Group by month using to_char function

SELECT aggregate_function (name_of_column), TO_CHAR (name_of_column, 'Month') FROM name_of_table GROUP BY TO_CHAR(date, 'Month');

Group by month using extract function

SELECT aggregate_function (name_of_column), EXTRACT(MONTH FROM name_of_column) FROM name_of_table GROUP BY EXTRACT (MONTH FROM name_of_column);

Below is the parameter description syntax of the group by month in PostgreSQL.

  • Select – Select operation is used to select the data from the table and convert it month-wise using the date_trunc, extract, and to_char functions in PostgreSQL. We have a select date column from the table to convert data into month-wise.
  • Date_trunc – This function converts the date column data month-wise by using the group by month in PostgreSQL. Date trunc will convert the data into the month.
  • Month – This variable was used with the date_trunc function to convert the date into the month format in PostgreSQL. We have used group by clause with the month in PostgreSQL.
  • Name of the column – This is defined as the name of the column we used with the date_trunc and to_char functions. We have converted date column data into the month-wise by using the group in PostgreSQL.
  • Count – This function is used to count the rows of data using the count function. Count function is an optional parameter of the date_trunc function.
  • Name of the table – This is defined as the name of the table we have used with the date_trunc and to_char functions in PostgreSQL. We have to convert date column table data to month-wise by using the group in PostgreSQL.
  • Aggregate function – We are using the aggregate function while using the to_char function. All the aggregate functions we are using with the to_char and date_trunc function.
  • To_char – We have used the to_char function to retrieve monthly data using an aggregate function in PostgreSQL.Using the to_char function, we have retrieved the month in character format.

How to perform group by month in PostgreSQL?

1. Below is the way to perform the group by month.

2. we have used the date_trunc and to_char functions to perform group by month.

3. We also use an aggregate function with date_trunc and to_char functions to use group by month.

4. We have used the below aggregate function with date_trunc and to_char function to use group by month.

  • Avg
  • Sum
  • Count
  • Min
  • Max

5. The below example shows that we have used an aggregate function with the group by month.

SELECT DATE_TRUNC('month',month_date), COUNT(1) AS count FROM month_test GROUP BY DATE_TRUNC('month',month_date);
SELECT sum(month_paid_out), TO_CHAR(month_date, 'Month') FROM month_test GROUP BY TO_CHAR(month_date, 'Month');

Output:

PostgreSQL group by month output 1

6. In the first example, we used the aggregate count function by using the date_trunc function to retrieve group data by month.

7. In the second example, we used the sum aggregate function by the to_char function to retrieve group data by month.

8. Basically date_trunc function is used to retrieve date and time with specific precision.

9. We have used two arguments with the date_trunc function: the date precision and the name of the column.

10. To use group by month, we need to define the month with the date parameter.

11. To_char is used to convert the integer into the sting using to_char, we have defining month in character format.

Examples of PostgreSQL group by month

Below is an example of the group by month. We have used the month_test table to describe the example of the group by month.

Below is the table and data description of the table month_test.

select * from month_test;
\d+ month_test;

Output:

PostgreSQL group by month output 2

Example #1 – by using the date_trunc function

The below example shows the group by month by using the date_trunc function.

In the first example, we have not used the aggregate function, in the second example, we have used the aggregate function.

SELECT DATE_TRUNC('month',month_date) FROM month_test GROUP BY DATE_TRUNC('month',month_date);
SELECT DATE_TRUNC('month',month_date), COUNT(*) AS count FROM month_test GROUP BY DATE_TRUNC('month',month_date);

Output:

PostgreSQL group by month output 3

Example #2 – by using the to_char function

The below example shows that group by month by using to_char function.

In the first example, we have used the sum aggregate function, in the second example, we have used the min aggregate function.

SELECT sum(month_paid_out), TO_CHAR(month_date, 'Month') FROM month_test GROUP BY TO_CHAR(month_date, 'Month');
SELECT MIN(month_paid_out), TO_CHAR(month_date, 'Month') FROM month_test GROUP BY TO_CHAR(month_date, 'Month');

Output:

output 4

Example #3 – by using the extract function

The below example shows the group by month by using the extract function.

In the first example, we have used the sum aggregate function, in the second example, we have used the max aggregate function.

SELECT SUM(month_paid_out), EXTRACT(MONTH FROM month_date) FROM month_test GROUP BY EXTRACT(MONTH FROM month_date);
SELECT MAX(month_paid_out), EXTRACT(MONTH FROM month_date) FROM month_test GROUP BY EXTRACT(MONTH FROM month_date);

Output:

output 5

Conclusion

We have used group by month using date_trunc, to_char, and extract function. PostgreSQL group by month is important to display the records of the specified month. Using the to_char function, month displays the character format, and extract and date_trunc function month will be displayed in an integer format.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL group by month” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL TO_DATE()
  2. Roles in PostgreSQL
  3. PostgreSQL TIME
  4. PostgreSQL JDBC Driver

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW