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.
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
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
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.
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
COUNT(emp_id) is used to get the number of employees of each gender.
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
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
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.
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
Then, it then executes the
SUM() function for each group of rows.
SELECT dept_id, gender, SUM(salary) FROM employee GROUP BY dept_id, gender;