How to Use the PostgreSQL GROUP BY Clause

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re executing queries in PostgreSQL, it’s important that your data is returned in an organized format. Sorting and grouping your results can make them more readable and help you understand your data better. One useful tool for organizing your query results is the GROUP BY clause, which allows you to create logical groups of records that have identical values for a given column. In this article, we’ll provide an overview of the PostgreSQL GROUP BY clause and provide some examples of its use.

Prerequisites

Before you proceed with this tutorial, make sure you have the PostgreSQL database server installed on your device. You can use the command psql -V to check the version number of the PostgreSQL interactive terminal.

The PostgreSQL GROUP by Clause

In PostgreSQL, the GROUP BY clause splits the rows retrieved from the SELECT statement, grouping together rows in a table that have similar results. Grouping records in this way minimizes redundancy in your output and makes it easy to calculate aggregates for the groups.

The basic syntax for the GROUP BY clause is shown below:

1
SELECT column_name, aggregate_function() FROM TABLE_NAME GROUP BY column_name;

The clause is used directly after the FROM or WHERE clause in the query. It is followed by a single column name or a set of columns that are separated by commas. You can also use the GROUP BY clause with an expression instead of a column name.

Examples Using PostgreSQL GROUP BY

Let’s assume that we have a table named employees in our database:

1
2
3
4
5
6
7
8
9
10
 id | firstname |  lastname  | age | salary
----+-----------+------------+-----+--------
 65 | Alfredo   | Brandon    |  26 |  25000
 36 | Janot     | Domigon    |  27 |  26000
 23 | Colmore   | Omar       |  30 |  30000
 22 | Holy      | Aether     |  24 |  15000
 33 | Gerri     | Foulspun   |  35 |  27000
 50 | Ingmar    | Eaglestone |  21 |  10000
 55 | Amalita   | Ofiler     |  27 |  35000
 10 | Adriana   | Matuschek  |  26 |  39000

We can get the data from the employees table and group the results by id:

1
SELECT id FROM employees GROUP BY id ORDER BY id ASC;

The expected output should be:

1
2
3
4
5
6
7
8
9
10
 id
----
 10
 22
 23
 33
 36
 50
 55
 65

Next, let’s try to get the total sum of salary of each employee in the table. We can use the GROUP BY clause to accomplish this:

1
SELECT id, firstname, SUM(salary) FROM employees GROUP BY id ORDER BY SUM(salary) DESC;

The result is:

1
2
3
4
5
6
7
8
9
10
id | firstname | sum
----+-----------+-------
10 | Adriana | 39000
55 | Amalita | 35000
23 | Colmore | 30000
33 | Gerri | 27000
36 | Janot | 26000
65 | Alfredo | 25000
22 | Holy | 15000
50 | Ingmar | 10000

NOTE: The sum() function will be applied to employees with the same name but different salaries. These salary values will be added together and returned as one value.

For our next example, we’ll add another record with the same name of Ingmar Eaglestone that already exists in our table:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | firstname | lastname | age | salary
----+-----------+------------+-----+--------
65 | Alfredo | Brandon | 26 | 25000
36 | Janot | Domigon | 27 | 26000
23 | Colmore | Omar | 30 | 30000
22 | Holy | Aether | 24 | 15000
33 | Gerri | Foulspun | 35 | 27000
50 | Ingmar | Eaglestone | 21 | 10000
55 | Amalita | Ofiler | 27 | 35000
10 | Adriana | Matuschek | 26 | 39000
51 | Ingmar | Eaglestone | 21 | 7500
52 | Ingmar | Eaglestone | 21 | 7500
53 | Ingmar | Eaglestone | 21 | 7500

We can then execute the SELECT statement:

1
SELECT firstname, SUM(salary) FROM employees GROUP BY firstname ORDER BY firstname;

The result should look like this:

1
2
3
4
5
6
7
8
9
10
firstname | sum
-----------+-------
Adriana | 39000
Alfredo | 25000
Amalita | 35000
Colmore | 30000
Gerri | 27000
Holy | 15000
Ingmar | 32500
Janot | 26000

We can see that the name Ingmar now has a total salary of 32500.

Next, let’s use the GROUP BY clause to count the number of id values with the same name in the table:

1
SELECT firstname, COUNT(id) FROM employees GROUP BY firstname;

The output will look like the following:

1
2
3
4
5
6
7
8
9
10
firstname | count
-----------+-------
Alfredo | 1
Colmore | 1
Adriana | 1
Holy | 1
Amalita | 1
Ingmar | 4
Janot | 1
Gerri | 1

Conclusion

Organizing the results of a PostgreSQL query can make them easier to read and help you gain valuable insights into your data. The GROUP BY clause allows you to create logical groups within your query results and use aggregate functions on the data. In this article, we discussed the PostgreSQL GROUP BY clause and provided examples to demonstrate the different ways the clause can be used. With our instructions and examples to get you started, you’ll be able to make use of the GROUP BY clause in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.