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
SELECTlist must either be in theGROUP BYclause 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()andMAX().
In the exercise, count the employees of each gender.