PostgreSQL Group By

What GROUP BY Does

When you run a query with GROUP BY, PostgreSQL partitions the result set into buckets based on one or more columns, then applies an aggregate function (COUNT, SUM, AVG, MIN, MAX) to each bucket independently. Instead of one number for the whole table, you get one number per group.

Every column in the SELECT list that is not wrapped in an aggregate function must appear in the GROUP BY clause. This is a hard rule in PostgreSQL: it will raise an error rather than silently pick an arbitrary row's value, which is what some other databases do.

Basic Syntax

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

Here, orders are bucketed by customer_id, and COUNT(*) tells you how many rows fell into each bucket. The ORDER BY runs after grouping, so you can sort by the aggregated value itself.

Grouping by Multiple Columns

You can group by more than one column to get finer-grained buckets. Each unique combination of values across the listed columns forms its own group.

SELECT region, EXTRACT(YEAR FROM order_date) AS order_year,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY region, EXTRACT(YEAR FROM order_date)
ORDER BY region, order_year;

Notice the expression EXTRACT(YEAR FROM order_date) is repeated in both the SELECT list and the GROUP BY clause. PostgreSQL also lets you reference the column by its position number, so GROUP BY 1, 2 would work identically here.

Common Aggregate Functions

• COUNT(*) — number of rows in each group, including NULLs
• SUM(column) — total of a numeric column within each group
• AVG(column) — arithmetic mean within each group
• MIN(column) / MAX(column) — smallest and largest value within each group
• ARRAY_AGG(column) — collects all values in a group into an array
• STRING_AGG(column, ', ') — concatenates text values in a group with a separator

SELECT department, COUNT(*) AS headcount,
       AVG(salary)::numeric(10,2) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees
GROUP BY department;