PostgreSQL GROUP BY clause (original) (raw)

Last Updated : 12 Jul, 2025

The **GROUP BY clause in **PostgreSQL is an essential tool that allows us to group rows that share the same values in one or more columns. This powerful functionality is commonly used to perform **aggregate calculations such as **SUM(), **COUNT(), **AVG(), and more, enabling us to summarize data efficiently.

In this article, we will explain the usage of the **GROUP BY clause in **PostgreSQL, explore practical examples, and understand how to effectively use it with aggregate functions.

What is PostgreSQL GROUP BY clause ?

The GROUP BY clause groups rows in a table based on the values of one or more specified columns. After **grouping, **aggregate functions such as **SUM(), **COUNT(), and **AVG() are applied to each group to calculate summary statistics. This allows us to generate more meaningful insights from our data. Whether we are analyzing **sales transactions, **counting occurrences, or **calculating averages, the **GROUP BY clause plays a key role in **SQL queries.

**Syntax

SELECT
column_1,
column_2,
computing_function(column_3)
FROM
table_name
GROUP BY
column_1,
column_2;

**Key Terms

**column_1, **column_2: Columns by which the data will be grouped.
**aggregate_function(column_3): A function such as **SUM(), **AVG(), or **COUNT() that operates on the grouped data.
**table_name: The name of the table from which the data is selected.
**GROUP BY: This clause groups the result set based on the values of the specified columns.

**Note: It is important to note that The GROUP BY clause must exactly appear after the **FROM or **WHERE clause. Additionally, any column that is not used in an aggregate function must appear in the **GROUP BY clause.

Examples of the **GROUP BY Clause in PostgreSQL

For the better understand of this article we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples. Let us take a look at some of the examples of the **GROUP BY clause in **PostgreSQL to better understand the concept.

**Example 1: Grouping Data by Customer ID

Here we will query for data from the payment table and group the result by '**customer_id' from the "**payment" table of our sample database. This query will return a list of **unique customer IDs.

**Query:

SELECT
customer_id
FROM
payment
GROUP BY
customer_id;

**Output

Grouping Data by Customer ID

**Example 2: Calculating Total Amount Paid by Each Customer

Here we will query to get the amount that each customer has paid till date and use an aggregate function (ie **SUM()), to do so and group them by '**customer_id' from the "payment" table of the sample database.

**Query:

SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id;

**Output

Calculating Total Amount Paid by Each Customer

Example 3: Counting Payment Transactions Processed by Each Staff

In this example, we will count the number of *payment transactions processed by each *staff member. We will group the rows in the payment table based on '*staff_id*' and use the **COUNT() function to get the number of **transactions. This query will return the **number of transactions processed by each **staff member.

**Query:

SELECT
staff_id,
COUNT (payment_id)
FROM
payment
GROUP BY
staff_id;

**Output

Counting Payment Transactions Processed by Each Staff

Important Points About GROUP BY clause in PostgreSQL

The GROUP BY clause is used to aggregate data based on one or more columns.
The GROUP BY clause must appear after the FROM and **WHERE**clauses in a SQL query. The order of execution ensures that data is filtered before being grouped.
Only the columns listed in the **GROUP BY**clause or aggregate functions can be included in the **SELECT**statement.
NULL values in **GROUP BY clauseare treated as a single group.

Conclusion

The **GROUP BY clause in PostgreSQL is a powerful feature for summarizing data based on one or more columns. By using aggregate functions like **SUM(), COUNT(), and AVG(), we can perform essential data analysis within our SQL queries. Whether we are summarizing payments, **counting transactions, or calculating **averages, mastering the **GROUP BY clause is essential for effective data analysis in PostgreSQL.