GROUP BY - (PostgreSQL Clause)

The GROUP BY clause is a PostgreSQL clause used to group the result set based on one or more columns. In this tutorial, we will learn how to use the GROUP BY clause in PostgreSQL.

Syntax

SELECT column1, column2, ... 
FROM table 
WHERE conditions 
GROUP BY column1, column2, ...;

• SELECT: specifies the columns that we want to retrieve from the table.
• FROM: specifies the table from which we want to retrieve the data.
• WHERE (optional): specifies the conditions that must be fulfilled for the rows to be retrieved.
• GROUP BY: groups the result set by one or more columns.

Example

Let's consider the following orders table:

order_id	customer_id	total_amount
1	1	50.00
2	2	30.00
3	2	20.00
4	1	25.00

The following example will illustrate how GROUP BY clause can be used to group the orders by customer_id and calculate the total amount spent by each customer:

SELECT customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id

The output of this query will be:

customer_id	sum
1	75.00
2	50.00

Explanation

In the above example, the GROUP BY clause is used to group the orders table by customer_id. The SUM function is then used to calculate the total amount spent by each customer.

The result set returned by this query contains two columns: customer_id and the total amount spent by each customer.

Use

The GROUP BY clause is useful when we want to group the results of a query based on one or more columns and calculate aggregate values such as SUM, COUNT, AVG, etc.

Important Points

• The columns selected in the SELECT clause must either be part of the GROUP BY clause or must be used with an aggregate function such as SUM, COUNT, AVG, etc.
• The order of the columns in the GROUP BY clause is important. If we change the order of the columns in the GROUP BY clause, the result set will be different.
• The HAVING clause can be used to filter results based on aggregated values.

Summary

In this tutorial, we learned how to use the GROUP BY clause in PostgreSQL to group the result set based on one or more columns and calculate aggregate values such as SUM, COUNT, AVG, etc. We discussed the syntax, example, output, explanation, use, and important points of the GROUP BY clause in PostgreSQL. With this knowledge, you can now use the GROUP BY clause in your PostgreSQL queries to retrieve aggregated data from your database.