Updated May 24, 2023
Introduction to PostgreSQL group by day
PostgreSQL group by day is used to retrieve the data by using day basis, we have applied group by clause on date_trunc function to retrieve table data as per day basis in PostgreSQL. If we need table data on per daily basis then we use PostgreSQL group by day in PostgreSQL. We are using date_trunc, group by, and aggregate functions to retrieve table data as per day basis in PostgreSQL, we are using date_trunc function on the column from which we are retrieving data as per day basis.
Syntax of PostgreSQL group by day
1. Group by day using the date_trunc function.
Select DATE_TRUNC ('day', name_of_column) count (name_of_column) from name_of_table GROUP BY DATE_TRUNC ('day', name_of_column);2. Group by day using the to_char function.
SELECT aggregate_function (name_of_column), TO_CHAR (name_of_column, 'day') FROM name_of_table GROUP BY TO_CHAR(date, 'day');Below is the parameter description syntax of the group by day in PostgreSQL:
- Select: Select operation is used to select the data from the table and convert it into day wise using the date_trunc and to_char functions in PostgreSQL. We have selected the date column from the table to convert data as per day wise.
- Date_trunc: This function converts the date column data day wise by using the group by day. Date trunc will convert the data per day wise.
- Day: This variable was used with the date_trunc function to convert the date into the day format. We have used group by clause with the day.
- 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 per day using the group by.
- Count: This function is used to count the rows of data using the count function in PostgreSQL. Count function is an optional parameter of the date_trunc function using group by day.
- 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 converted the date column table data per day using the group by day.
- 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 functions.
- To_char: We used the to_char function to retrieve data daily using the aggregate function in PostgreSQL. Using the to_char function, we are retrieving day in character format.
How to Perform group by day in PostgreSQL?
We use the date_trunc and to_char functions to perform group by day in PostgreSQL. We also use an aggregate function with date_trunc and to_char function to use group by day.
Use the below aggregate function with date_trunc and to_char function to use group by day in PostgreSQL.
- Avg
- Sum
- Count
- Min
- Max
The below example shows that we are using an aggregate function.
Code:
SELECT DATE_TRUNC('day', day_date), COUNT(1) AS count FROM day_test GROUP BY DATE_TRUNC('day', day_date);Output:
Code:
SELECT sum(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');
SELECT max(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');
SELECT min(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');Output:
In the first example, we used the aggregate count function by using the date_trunc function to retrieve group data by day. Second example, we used the sum aggregate function by the to_char function to retrieve group data by day. In the third example, we used the max aggregate function by using the to_char function to retrieve group data by day. In the fourth example, we used the min aggregate function by the to_char function to retrieve group data by day. We are using a two-argument with the date_trunc function first is date precision, and the second is the column’s name. To use group by with day, we need to define the day with the date parameter. To_char is used to convert the integer into the sting using to_char, we have defining day in character format.
Examples
Given below are the examples of the group by day in PostgreSQL. We are using the day_test table to describe the example.
Below is the table and data description of table day_test.
Code:
select * from day_test;
\d+ day_test;Output:
Example #1
Using date_trunc function.
The below example shows using the date_trunc function.
a. Without using the aggregate function.
Code:
SELECT DATE_TRUNC('day',day_date) FROM day_test GROUP BY DATE_TRUNC('day',day_date);Output:
b. Using the aggregate function.
Code:
SELECT DATE_TRUNC('day',day_date), COUNT(*) AS count FROM day_test GROUP BY DATE_TRUNC('day',day_date);Output:
Example #2
By using the to_char function.
The example below shows that PostgreSQL group by day using the to_char function.
a. To_char function by using sum aggregate function.
Code:
SELECT sum(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');Output:
b. To_char function by using avg aggregate function.
Code:
SELECT AVG(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');Output:
c. To_char function by using min aggregate function.
Code:
SELECT MIN(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');Output:
d. To_char function by using the max aggregate function.
Code:
SELECT MAX(day_paid_out), TO_CHAR(day_date, 'day') FROM day_test GROUP BY TO_CHAR(day_date, 'day');Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL group by day” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
