PostgreSQL - GROUP BY

PostgreSQL - The GROUP BY Clause

The PostgreSQL GROUP BY clause is a powerful feature used with the SELECT statement to collect and condense multiple rows with identical values in one or more columns into a single summary row. Its primary purpose is to perform aggregate calculations on these groups, such as counting items, summing values, or finding averages.

In a SELECT query, the GROUP BY clause is placed after the WHERE clause and before the HAVING or ORDER BY clauses.

Core Concepts

To use GROUP BY effectively, you must understand two key components:

• Grouping Columns: These are the columns whose values are used to create the groups. All rows with the same value in the grouping column(s) are placed into a single group.
• Aggregate Functions: These functions perform a calculation on a set of values and return a single value. Common aggregate functions include COUNT(), SUM(), AVG(), MAX(), and MIN().

Syntax

The fundamental syntax of the GROUP BY clause is as follows:

SELECT
    column1,
    column2,
    aggregate_function(column3)
FROM
    table_name
WHERE
    [conditions]
GROUP BY
    column1, column2
ORDER BY
    column1, column2;

The Golden Rule of GROUP BY: Any column in the SELECT list that is not enclosed in an aggregate function must be included in the GROUP BY clause. Failing to do so will result in a syntax error.

Practical Example

Let’s use a more practical employees table for our example. Assume it has the following structure and data:

-- Table: employees
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 90000),
('Bob', 'Engineering', 85000),
('Charlie', 'HR', 60000),
('David', 'Sales', 75000),
('Eve', 'Sales', 78000),
('Frank', 'HR', 62000);

Now, let’s find the number of employees, the average salary, and the total salary for each department.

SELECT
    department,
    COUNT(*) AS number_of_employees,
    AVG(salary)::NUMERIC(10, 2) AS average_salary,
    SUM(salary) AS total_salary_pool
FROM
    employees
GROUP BY
    department
ORDER BY
    department;

This query produces a clear, aggregated summary:

 department  | number_of_employees | average_salary | total_salary_pool
-------------+-----------------------+----------------+---------------------
 Engineering |                     2 |       87500.00 |            175000.00
 HR          |                     2 |       61000.00 |            122000.00
 Sales       |                     2 |       76500.00 |            153000.00
(3 rows)

Filtering Groups with HAVING

While the WHERE clause filters rows before they are grouped, the HAVING clause filters the groups after aggregation. This is useful for asking questions about the groups themselves.

For example, let’s find only the departments where the average salary is greater than $70,000.

SELECT
    department,
    AVG(salary)::NUMERIC(10, 2) AS average_salary
FROM
    employees
GROUP BY
    department
HAVING
    AVG(salary) > 70000
ORDER BY
    average_salary DESC;

The result will now only include the departments meeting the HAVING condition:

 department  | average_salary
-------------+----------------
 Engineering |       87500.00
 Sales       |       76500.00
(2 rows)

Common Pitfalls and Debugging

• Error: column ”…” must appear in the GROUP BY clause or be used in an aggregate function. This is the most common error. It means you have a column in your SELECT list that is neither an aggregate nor part of the GROUP BY list. Add it to the GROUP BY clause to fix it.
• Misunderstanding WHERE vs. HAVING: Remember, WHERE filters individual rows before grouping. HAVING filters entire groups after aggregation. You cannot use an aggregate function like SUM() in a WHERE clause.