How to Use the PostgreSQL GROUP BY Clause
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