GROUP BY: Group Rows and Summarize

The GROUP BY clause groups rows that have the same value in a column.
It is used with aggregate functions (like COUNT(), SUM() and AVG()) to summarize data.

Note: You will learn these functions in detail in the next section.

Syntax

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

Example 1: Number of Employees per Department

SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY department;

Output:

 department | count 
------------+-------
 Finance    |     4
 HR         |     2
 IT         |     6
 Marketing  |     2
 Sales      |     6
(5 rows)

This returns the number of employees in each department. COUNT(*) counts the rows in each group.

Notice the ORDER BY again. PostgreSQL does not return groups in any particular order, so add ORDER BY when the order matters.

Example 2: Oldest Age per Department

SELECT department, MAX(age)
FROM employees
GROUP BY department
ORDER BY department;

Output:

 department | max 
------------+-----
 Finance    |  33
 HR         |  28
 IT         |  33
 Marketing  |  30
 Sales      |  31
(5 rows)

MAX(age) finds the largest age in each group.


Tip

  • Every column in your SELECT list must either be in the GROUP BY clause or be inside an aggregate function. If it is not, PostgreSQL gives an error:
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

Output:

ERROR:  column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT department, name, COUNT(*)
                           ^

PostgreSQL cannot show one name for a whole group of employees, so it stops with an error.

  • Common aggregate functions: COUNT(), SUM(), AVG(), MIN() and MAX().

In the exercise, count the employees of each gender.