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.
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 by using the date_trunc, extract, and to_char function in PostgreSQL. We have a select date column from the table to convert data into month-wise.
- Date_trunc – This function is used to convert 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 is a variable that was used with date_trunc function to convert 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 which was we have used with the date_trunc and to_char function. 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 by using the count function. Count function is optional parameter of date_trunc function.
- Name of the table – This is defined as the name of the table which was we have used with the date_trunc and to_char function in PostgreSQL. We have to convert date column table data into the month-wise by using the group by in PostgreSQL.
- Aggregate function – We are using aggregate function while using 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 data on a monthly basis by using an aggregate function in PostgreSQL.Using the to_char function we have retrieved month in character format.
How to perform group by month in PostgreSQL?
1. Below is the way to perform the group by month.
2. Basically we have used the date_trunc and to_char function to perform group by month.
3. We are also using an aggregate function with date_trunc and to_char function 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');
6. In the first example, we have used the count aggregate function by using the date_trunc function to retrieve data of group by month.
7. In the second example, we have used the sum aggregate function by using the to_char function to retrieve data of group by month.
8. Basically date_trunc function is used to retrieve date and time with specific precision.
9. We have used two arguments with date_trunc function first is date precision and the second is the name of the column.
10. To use group by month then we need to define the month with the parameter of the date.
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 the 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;
Example #1 – by using date_trunc function
The below example shows the group by month by using 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);
Example #2 – by using 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');
Example #3 – by using 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);
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 to_char function month is display the character format and using extract and date_trunc function month will be displayed in an integer format.
Recommended Articles
This is a guide to the PostgreSQL group by month. Here we discuss How to perform group by month in PostgreSQL and Examples along with the codes and outputs. You may also have a look at the following articles to learn more –
