PostgreSQL – GROUP BY

GROUP BY clause is used in a SELECT statement to group “like” records. You can specify one or more columns in the GROUP BY clause. It is used with aggregate functions – to obtain 1 value for a group of rows.

Syntax:

SELECT column1, agg_func(column2) FROM table_name GROUP BY column1;

Consider the employee table as shown below:

idfirst_namelast_namedepartmentsalary
101bryanwalkersales50,000
102alexcarpenteraccounts55,000
103samwhitesales60,000
104johnbarrysales54,000
105billwoodaccounts65,000
106roberthopkinssupport70,000
107davidleesupport72,000
108jacksearssupport65,000
109tomyoungsales55,000

You can use GROUP BY clause to obtain maximum salary in each department as shown below:

SELECT department, max(salary) FROM employee GROUP BY department;

department    max
----------   ------
sales        60000
accounts     65000
support      72000

Get count of employees in each department as below:

SELECT department, count(id) FROM employee GROUP BY department;

department    count
----------   ------
sales        4
accounts     2
support      3

See also  PostgreSQL - Get definition of a materialized view