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 day
 

PostgreSQL group by day

Priya Pedamkar
Article byPriya Pedamkar

Updated May 24, 2023

PostgreSQL group by day

 

 

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.

Watch our Demo Courses and Videos

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

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:

PostgreSQL group by day 1

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:

PostgreSQL group by day 2

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:

PostgreSQL group by day 3

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:

Without using aggregate function

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:

PostgreSQL group by day 5

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:

using sum aggregate function

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:

To_char function by using avg aggregate function

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:

PostgreSQL group by day 8

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:

PostgreSQL group by day 9

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.

  1. Postgresql Count
  2. PostgreSQL Describe Table
  3. Caching in PostgreSQL
  4. PostgreSQL MAX

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