Introduction to PostgreSQL GROUP BY clause

TheGROUP BY clause divides the rows returned from theSELECT statement into groups.

For each group, you can apply anaggregate function such asSUM() to calculate the sum of items orCOUNT() to get the number of items in the groups.

The following illustrates the basic syntax of theGROUP BY clause:

SELECT   column_1,   column_2,   ...,   aggregate_function(column_3)FROM   table_nameGROUP BY   column_1,   column_2,   ...;

In this syntax:

• First, select the columns that you want to group such ascolumn1 andcolumn2, and column that you want to apply an aggregate function (column3).
• Second, list the columns that you want to group in theGROUP BY clause.

TheGROUP BY clause divides the rows by the values in the columns specified in theGROUP BY clause and calculates a value for each group.

It’s possible to use other clauses of theSELECT statement with theGROUP BY clause.

PostgreSQL evaluates theGROUP BY clause after theFROM andWHERE clauses and before theHAVINGSELECT,DISTINCT,ORDER BY andLIMIT clauses.

PostgreSQL GROUP BY clause examples

Let’s take a look at thepayment table in thesample database.

1) Using PostgreSQL GROUP BY without an aggregate function example

The following example uses theGROUP BY clause to retrieve thecustomer_id from the payment table:

SELECT  customer_idFROM  paymentGROUP BY  customer_idORDER BY  customer_id;

Output:

customer_id-------------           1           2           3           4           5           6           7           8...

Each customer has one or more payments. TheGROUP BY clause removes duplicate values in the customer_id column and returns distinct customer ids. In this example, theGROUP BY clause works like theDISTINCT operator.

2) Using PostgreSQL GROUP BY with SUM() function example

TheGROUP BY clause is useful when used in conjunction with anaggregate function.

The following query uses theGROUP BY clause to retrieve the total payment paid by each customer:

SELECT  customer_id,  SUM (amount)FROM  paymentGROUP BY  customer_idORDER BY  customer_id;

Output:

customer_id |  sum-------------+--------           1 | 114.70           2 | 123.74           3 | 130.76           4 |  81.78           5 | 134.65           6 |  84.75           7 | 130.72...

In this example, theGROUP BY clause groups the payments by the customer id. For each group, it calculates the total payment.

The following statement uses theORDER BY clause withGROUP BY clause to sort the groups by total payments:

SELECT  customer_id,  SUM (amount)FROM  paymentGROUP BY  customer_idORDER BY  SUM (amount) DESC;

Output:

customer_id |  sum-------------+--------         148 | 211.55         526 | 208.58         178 | 194.61         137 | 191.62         144 | 189.60

3) Using PostgreSQL GROUP BY clause with the JOIN clause

The following statement uses theGROUP BY clause to retrieve the total payment for each customer and display the customer name and amount:

SELECT  first_name || ' ' || last_name full_name,  SUM (amount) amountFROM  payment  INNER JOIN customer USING (customer_id)GROUP BY  full_nameORDER BY  amount DESC;

Output:

full_name       | amount-----------------------+-------- Eleanor Hunt          | 211.55 Karl Seal             | 208.58 Marion Snyder         | 194.61 Rhonda Kennedy        | 191.62 Clara Shaw            | 189.60...

In this example, we join thepayment table with thecustomer table using aninner join to get the customer names and group customers by their names.

4) Using PostgreSQL GROUP BY with COUNT() function example

The following example uses theGROUP BY clause with theCOUNT() function to count the number of payments processed by each staff:

SELECTstaff_id,COUNT (payment_id)FROMpaymentGROUP BYstaff_id;

Output:

staff_id | count----------+-------        1 |  7292        2 |  7304(2 rows)

In this example, theGROUP BY clause divides the rows in thepayment table into groups and groups them by value in thestaff_id column. For each group, it counts the number of rows using theCOUNT() function.

5) Using PostgreSQL GROUP BY with multiple columns

The following example uses aGROUP BY clause to group rows by values in two columns:

SELECT  customer_id,  staff_id,  SUM(amount)FROM  paymentGROUP BY  staff_id,  customer_idORDER BY  customer_id;

Output:

customer_id | staff_id |  sum-------------+----------+--------           1 |        2 |  53.85           1 |        1 |  60.85           2 |        2 |  67.88           2 |        1 |  55.86           3 |        1 |  59.88...

In this example, theGROUP BY clause divides the rows in thepayment table by the values in thecustomer_id andstaff_id columns. For each group of(customer_id, staff_id), theSUM() calculates the total amount.

6) Using PostgreSQL GROUP BY clause with a date column

The following example uses theGROUP BY clause to group the payments by payment date:

SELECT  payment_date::date payment_date,  SUM(amount) sumFROM  paymentGROUP BY  payment_date::dateORDER BY  payment_date DESC;

Output:

payment_date |   sum--------------+--------- 2007-05-14   |  514.18 2007-04-30   | 5723.89 2007-04-29   | 2717.60 2007-04-28   | 2622.73...

Since the values in thepayment_date column aretimestamps, we cast them to date values using thecast operator::.

Summary

• Use the PostgreSQLGROUP BY clause to divide rows into groups and apply an aggregate function to each group.

Last updated onApril 19, 2024

On this page

• Introduction to PostgreSQL GROUP BY clause
• PostgreSQL GROUP BY clause examples
• Summary