PostgreSQL: GROUP BY Clause

In PostgreSQL, the GROUP BY clause is used to form the group of rows in a table that have the same values in the columns specified with the GROUP BY clause. The groups can be formed on one or more columns. For example, the GROUP BY query will be used to count the number of Female or Male employees or to get the Gender wise total salaries.

You can use aggregate functions such as COUNT(), MAX(), MIN(), SUM(), AVG(), etc. to calculate the group-wise summary in the SELECT query. The result of the GROUP BY clause returns a single row for each value of the GROUP BY column.

Syntax: 

SELECT <column_list>
FROM <table_name>
[ WHERE <conditions>]
[ GROUP BY <column1>, <column2>...]
[ HAVING condition ]
[ ORDER BY ]

        

You can only include columns with the SELECT clause that are used in the GROUP BY clause. PostgreSQL evaluates FROM clause first, then the WHERE clause, and then the GROUP BY clause is evaluated before the HAVING, SELECT, ORDER BY, and LIMIT clauses.

Let's see how to use the GROUP BY clause using the following employee table.

PostgreSQL allows using GROUP BY clause without specifying any aggregate function. For example, The following query will return the list of all genders from the employee table.

Example: GROUP BY

SELECT gender FROM employee GROUP BY gender;

The following shows the result in pgAdmin.

Note that the SELECT clause can only include columns that are used with the GROUP BY clause. The gender column is used after the SELECT because it is used with the GROUP BY clause. It returns each distinct values in the gender column on which the groups are formed.

GROUP BY with Aggregate Functions

The GROUP BY clause is more useful when it is used with an aggregate function. For example, here we will find the total number of Male and Female employees from the employee table. The COUNT(emp_id) is used to get the number of employees of each gender.

Example: GROUP BY

SELECT gender, COUNT(emp_id) FROM employee GROUP BY gender;

In the above example, a query forms groups of distinct genders. There are two values in the gender column of the employee table, M and F. So, two groups will be formed and then the SELECT clause will return the gender column and also the result of COUNT(emp_id) functions for each group. The result shows that there are 3 males (M) and 4 females (F) in the employee table.

GROUP BY with SUM()

The following query uses the SUM() function in the SELECT clause with the GROUP BY clause. It returns the sum of the salary in each group.

Example: GROUP BY

SELECT gender, SUM(salary) FROM employee
GROUP BY gender;

GROUP BY on Multiple Columns

The GROUP BY clause can be applied to multiple columns also. The following forms two groups, first on the dept_id and second on the gender column. Then, it then executes the SUM() function for each group of rows.

Example: GROUP BY

SELECT dept_id, gender, SUM(salary) FROM employee
GROUP BY dept_id, gender;