PostgreSQL: Using the GROUP BY Clause for Aggregate Queries

Group rows and calculate aggregate values (SUM, COUNT, AVG) using PostgreSQL's GROUP BY clause. This guide explains its syntax and usage.

Using PostgreSQL's `GROUP BY` Clause

The PostgreSQL `GROUP BY` clause is used in conjunction with the `SELECT` statement to group rows that have the same values in specified columns into summary rows, like sums or counts. This is very helpful for generating reports or summaries of data.

Understanding the `GROUP BY` Clause

The `GROUP BY` clause groups rows with matching values in specified columns into summary rows. You can then use aggregate functions (like `SUM()`, `COUNT()`, `AVG()`, `MIN()`, `MAX()`) to calculate values for each group.

`GROUP BY` Clause Syntax

SELECT column1, column2, ... , aggregate_function(columnN)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;

The `GROUP BY` clause comes after the `WHERE` clause (if present) and before the `ORDER BY` clause (if present). The columns listed in the `GROUP BY` clause must also appear in the `SELECT` list.

Examples

(Note: The examples below assume the existence of an `employee` table with columns `emp_id`, `first_name`, `last_name`, and `salary`. The "Test it Now" links are placeholders; to execute these queries, you would need a PostgreSQL database with a similar table structure. Screenshots from the original text are not included here. Please refer to the original document for visual verification of the examples. The descriptions below aim to convey the information present in those screenshots.)

Example 1: `GROUP BY` Without Aggregate Functions

This example groups rows by `emp_id`. It's similar to using `DISTINCT` but maintains the original row order within each group:

SELECT emp_id FROM employees GROUP BY emp_id;

Example 2: `SUM()` with `GROUP BY` and `WHERE`

This example calculates the sum of salaries for employees named 'John':

SELECT first_name, SUM(salary) 
FROM employees
WHERE first_name = 'John'
GROUP BY first_name;

Example 3: `SUM()` with `GROUP BY` and `ORDER BY`

This example calculates the sum of salaries for each employee, sorted in ascending order:

SELECT first_name, SUM(salary) 
FROM employees
GROUP BY first_name
ORDER BY SUM(salary) ASC;

Example 4: `GROUP BY` with `JOIN`

This example joins two tables and groups by employee's full name:

SELECT first_name || ' ' || last_name AS full_name, SUM(salary)
FROM employees
INNER JOIN employee_details USING (emp_id)
GROUP BY full_name
ORDER BY SUM(salary);

Example 5: `COUNT()` with `GROUP BY`

This example counts the number of employees for each first name:

SELECT first_name, COUNT(*) 
FROM employees
GROUP BY first_name;

Example 6: `GROUP BY` with Multiple Columns

This example groups by both `emp_id` and `first_name`:

SELECT emp_id, first_name, SUM(salary)
FROM employees
GROUP BY emp_id, first_name
ORDER BY emp_id;