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 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 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 group by day in PostgreSQL:
- Select: Select operation is used to select the data from the table and convert it into day wise by using the date_trunc and to_char function in PostgreSQL. We have selected the date column from the table to convert data as per day wise.
- Date_trunc: This function is used to convert the date column data as per day wise by using the group by day. Date trunc will convert the data as per day wise.
- Day: This is a variable that 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 which was we have used with the date_trunc and to_char function. We have converted date column data as per day wise by using the group by.
- Count: This function is used to count the rows of data by using the count function in PostgreSQL. Count function is an optional parameter of date_trunc function by using group by day.
- 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 converted date column table data as per day wise by using the group by day.
- 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 per daily basis by 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?
Basically, we are using the date_trunc and to_char function to perform group by day in PostgreSQL. We are also using an aggregate function with date_trunc and to_char function to use group by day.
We are using 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 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 have used the count aggregate function by using the date_trunc function to retrieve data of group by day. In the second example, we have used the sum aggregate function by using the to_char function to retrieve data of group by day. In the third example, we have used the max aggregate function by using the to_char function to retrieve data of group by day. In the fourth example, we have used the min aggregate function by using the to_char function to retrieve data of group by day. We are using a two-argument with the date_trunc function first is date precision and the second is the name of the column. To use group by with day then we need to define the day with the parameter of the date. 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 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 below example shows that PostgreSQL group by day by 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 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
This is a guide to PostgreSQL group by day. Here we discuss the introduction, how to perform group by day in PostgreSQL? and examples respectively. You may also have a look at the following articles to learn more –
