The GROUP BY clause in PostgreSQL is used with the collaboration of the SELECT statement to group several items. It is used to group several rows having identical/corresponding data returned by the SELECT statement. In most cases, the GROUP BY clause is used to remove redundancy and to calculate aggregates. The GROUP BY clause can be used with different functions like SUM() and COUNT(), etc., to perform various functionalities on the group items.

How to Use GROUP BY Clause in Postgres?

We can define an individual or multiple columns in the Postgres GROUP BY clause. Follow the comma-separated syntax to specify multiple columns within the GROUP BY clause:

SELECT list_of_columns
FROM tab_name
GROUP BY col_1, col_2....col_N

Let’s illustrate the above syntax step-by-step:

● In the SELECT statement, specify a column list that you want to group.

● tab_name is a table to which the selected columns belong.

● col_1, col_2, …, col_N represent the columns to be grouped.

You can follow the following hierarchy to specify the different clauses of the SELECT statement, such as WHERE, ORDER BY, etc.

- The FROM clause and WHERE clause will come prior to the GROUP BY clause.

- The HAVING, DISTINCT, ORDER BY, and LIMIT clauses will come after the GROUP BY clause.

Let’s head into the practical implementation of the Postgres GROUP BY clause:

Example 1: A Basic Example of GROUP BY Clause

A table named "bike_details" has already been created that contains the following records:

Select * from bike_details;

The below-given query will get the record of the selected table, and it will group the result based on bike_model:

SELECT bike_model FROM bike_details
GROUP BY bike_model;

The output shows that the GROUP BY clause has eliminated the duplicated/redundant records.

Example 2: How to Use the Postgres GROUP BY Clause Along With the SUM()?

Let’s run the below-given query to find the sum of group items using the SUM() function:

SELECT bike_model, SUM (bike_price)
FROM bike_details
GROUP BY bike_model;

In this example, we utilized the GROUP BY clause to group the bikes with respect to their model. Next, we utilized an aggregate function named SUM() that calculated the sum of group items.

Example 3: How to Use the Postgres GROUP BY Clause Along With the COUNT()?

In this example, we will use the COUNT() function to count the number of bikes available for each model:

SELECT bike_model, COUNT (bike_model)
FROM bike_details
GROUP BY bike_model;

The output shows that in the result set, there are two bikes for the 2022 and 2021 models.

Example 4: How Does ORDER BY Clause Work Along With the GROUP BY Clause?

In Postgres, various clauses of the SELECT statement can be used with the GROUP BY clause, such as ORDER BY, WHERE, etc. In this example, we will use the ORDER BY clause with the GROUP BY clause to sort and group the bike_model column in descending order:

SELECT bike_model
FROM bike_details
GROUP BY bike_model
ORDER BY bike_model DESC;

The output verified that this time the GROUP CLAUSE removed the redundant data and sorted the result-set in descending order.

That was all the basic information regarding the Postgres GROUP BY clause.

Conclusion

The GROUP BY clause in PostgreSQL is used with the collaboration of the SELECT statement to group several items. The GROUP BY clause, in most cases, is used to group identical rows, remove redundancy, and compute aggregates. To perform various functions on the group items, the GROUP BY clause can be combined with functions such as SUM() and COUNT(). Moreover, different clauses like the WHERE clause, ORDER BY clause, etc. This write-up explained the working of the GROUP BY clause by considering multiple examples.