PostgreSQL GROUP BY

GROUP BY Clause

The group by clause is used to divide the rows in a table into smaller groups that have the same values in the specified columns. This clause is used with a SELECT statement to combine a group of rows based on the values or a particular column or expression. Aggregate functions are used to return summary information for each group.

The WHERE clause is used to conditionally retrieve rows from a table, hence, it can not be applied to grouped result.

Syntax:

SELECT [DISTINCT] <column_list>| <expression>
FROM <table>[,<table>][WHERE <condition>]
GROUP BY <column | expression>
[HAVING <condition>] 
<condition>

Parameters

PostgreSQL GROUP BY example1

The sample table

If we want to get the department numbers and number of employees in each department in the employee table, the following SQL can be used.

SQL

Code:

SELECT deptno, COUNT(*)
FROM employee
GROUP BY deptno;

Output:

PostgreSQL GROUP BY example2

If we want to get the department number and the total salary payable for each department in the employee table, the following SQL can be used.

SQL

Code:

SELECT deptno "Department No", 
SUM(salary) "Salary Payable"
FROM employee
GROUP BY deptno;

Output:

PostgreSQL GROUP BY and ORDER BY

If we want to list the designation and the number of employees in each designation and show the result in ascending order of the number of employees from employee table, the following SQL can be used. The default order of ORDER BY is ascending.

SQL

Code:

SELECT designame "Designation", 
COUNT(*) "Number of Employees"
FROM employee
GROUP BY designame
ORDER BY COUNT(*);

Output:

PostgreSQL GROUP BY and ORDER BY in descending order

If we want to list the designation and the number of employees in each designation and show the result in descending order of the number of employees from employee table, the following SQL can be used.

SQL

Code:

SELECT designame "Designation", 
COUNT(*) "Number of Employees"
FROM employee
GROUP BY designame
ORDER BY 2 DESC;

Output:

Explanation

The ORDER BY in the above example followed by 2 DESC have been used. Here 2 i.e. [ COUNT(*) ] indicates the column number in which result is being ordered and DESC indicates the order in descending.

PostgreSQL GROUP BY with MAX, MIN, SUM, AVG

If we want to list the total salary, maximum and minimum salary and the average salary of employees according to designation from employee table, the following SQL can be used.

SQL

Code:

SELECT designame "Desig.", 
SUM(salary) "Salary",
AVG(salary) "Avg. Salary",
MAX(salary) "Max. Salary",
MIN(salary) "Min. Salary"
FROM employee
GROUP BY designame;

Output:

PostgreSQL GROUP BY with WHERE

If we want to list the average salary for each designation excluding 'PRESIDENT', the following SQL can be used.

SQL

Code:

SELECT designame "Designation", 
AVG(salary) "Average Salary"
FROM employee
WHERE designame <>'PRESIDENT'
GROUP BY designame;

Output:

Previous: DISTINCT Clause
Next: ORDER BY