PostgreSQL group by with examples

In this PostgreSQL tutorial, we will learn about the PostgreSQL group by with a few examples and cover the following topics.

  • PostgreSQL group by
  • PostgreSQL group by multiple columns
  • PostgreSQL group by one column
  • PostgreSQL group by month
  • PostgreSQL group by aggregate functions
  • PostgreSQL group by having
  • PostgreSQL group by month and year
  • PostgreSQL group by date
  • PostgreSQL group by hour

Postgresql group by

The PostgreSQL GROUP BY clause can divide the rows returned from the SELECT statement into groups. For each and every group, we can apply an aggregate function like the SUM function to calculate the sum of items or the COUNT function to get the number of items in the groups.

The basic syntax of the PostgreSQL GROUP BY clause is given below.

SELECT 
   col_1, 
   col_2,
   ...,
   aggregate_function(col_3)
FROM 
   name_of_table
GROUP BY 
   col_1,
   col_2,
   ...;

In the above syntax:

First, select the columns that we want to group like col_1 and col_2, and column that we want to apply an aggregate function (col_3).

Second, list the columns that we want to group in the GROUP BY clause.

The evaluation of the GROUP BY clause is done after the FROM and WHERE clauses and before the HAVING SELECT, ORDER BY, DISTINCT, and LIMIT clauses.

Postgresql group by multiple columns

In PostgreSQL, the GROUP BY clause can divide the rows based on multiple columns.

The table we will use here is emp_info and the description of the table is given below.

PostgreSQL group by
decription of emp_info

In the above table look at the address column that contains the states and cities of the United States like California, New York, Boston.

Let’s run the below query to group by the result address, salary_$, name.

SELECT name,address,salary_$ FROM emp_info
GROUP BY address,salary_$,name;

In the above code, we are selecting the columns name, address, salary_$ using the SELECT statement. Then divide the result into groups address, salary_$, name using the GROUP BY clause.

The output of the above code is given below.

Postgresql group by multiple columns
Postgresql group by multiple columns

This is an example of the PostgreSQL group by multiple columns.

PostgreSQL group by one column

In PostgreSQL, in the above sub-section “Postgresql group by multiple columns“, we grouped by multiple columns, but we can also group by only one column.

Let’s run the below code.

SELECT address FROM emp_info
GROUP BY address;

In the above code, we are grouping the rows by address.

The output of the above code is given below.

Postgresql group by one column
Postgresql group by one column

This is an example of the PostgreSQL group by one column.

PostgreSQL group by month

In PostgreSQL, we can also group by the rows of the table in a month using the GROUP BY clause.

The table we will use here is employees and the description of the table is given below.

Postgresql group by month
description of employee

Let’s run the below code to count the employe hire_date.

SELECT
       DATE_TRUNC('month',hire_date)
         AS  hire_date_to_month,
       COUNT(emp_no) AS count
FROM employee
GROUP BY DATE_TRUNC('month',hire_date);

The output of the above code is given below.

Postgresql group by month example
Postgresql group by month

This is an example of the PostgreSQL group by month.

PostgreSQL group by aggregate functions

In PostgreSQL, the group by function can divide the rows generated by aggregate functions. The aggregate function is used to return a single result from multiple rows. There are many aggregate function like SUM(), COUNT(),MIN(), MAX().

Let’s run the below code to show the minimum hire_date of employees’ names.

SELECT first_name,min(hire_date)
FROM employee
GROUP BY first_name;

The output of the above code is given below.

Postgresql group by aggregate functions
Postgresql group by aggregate functions

This is an example of the PostgreSQL group by aggregate functions.

PostgreSQL group by having

In PostgreSQL, we can use having a clause with the group by clause to apply filter or condition on a group of results or rows.

We will run the above sub-section query again with little change by adding a having clause to that.

Let’s run the below code to show the minimum hire_date of employees’ names greater than ‘1985-03-18’.

SELECT first_name,min(hire_date)
FROM employee
GROUP BY first_name
HAVING min(hire_date)>'1985-03-18';

The output of the above code is given below.

Postgresql group by having
Postgresql group by having

This is an example of a PostgreSQL group by having.

PostgreSQL group by month and year

In PostgreSQL, we can also group by the rows of the table in a month and year using the GROUP BY clause.

Let’s run the below code to count the employe hire_date in month and year.

SELECT
       DATE_TRUNC('month',hire_date)
         AS  hire_date_to_month,
		DATE_TRUNC('year',hire_date)
         AS  hire_date_to_year,
       COUNT(emp_no) AS count
FROM employee
GROUP BY DATE_TRUNC('month',hire_date),DATE_TRUNC('year',hire_date);

The output of the above code is given below.

Postgresql group by month and year
Postgresql group by month and year

Here is an example of the PostgreSQL group by month and year.

Read Postgresql date to string

PostgreSQL group by date

In PostgreSQL, the group by clause can group the columns that have a data type of date.

Let’s run the below code to group the date column name hire_date of table employee.

SELECT date(hire_date) FROM employee GROUP BY date(hire_date)
ORDER BY min(hire_date);

The output of the above code is given below.

Postgresql group by date
Postgresql group by date

This is an example of PostgreSQL group by date.

PostgreSQL group by hour

In PostgreSQL, as in the above sub-sections, we have grouped the rows or records by month, year, date. we can also group by the hour.

Let’s run the below code.

Postgresql group by hour
Postgresql group by hour

This is an example of the PostgreSQL group by hour.

You may like the following PostgreSQL tutorials:

This tutorial covers, PostgreSQL group by and the following topics.

  • PostgreSQL group by
  • PostgreSQL group by multiple columns
  • PostgreSQL group by one column
  • PostgreSQL group by month
  • PostgreSQL group by aggregate functions
  • PostgreSQL group by having
  • PostgreSQL group by month and year
  • PostgreSQL group by date
  • PostgreSQL group by hour