How to Use GROUP BY in PostgreSQL

How do I use GROUP BY in PostgreSQL to aggregate rows?

GROUP BY aggregates rows sharing the same column values so you can compute totals, averages, counts, and other statistics per group.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does GROUP BY do in PostgreSQL?

GROUP BY collapses rows that share the same value(s) in specified column(s) into a single group, allowing aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to run once per group instead of once per row.

How does GROUP BY work with aggregate functions?

After grouping, PostgreSQL applies the aggregate functions listed in the SELECT clause to each group. Columns in SELECT that are not wrapped in an aggregate must appear in the GROUP BY list.

What is the basic GROUP BY syntax?

Use SELECT, include aggregates or grouping columns, add GROUP BY followed by the same non-aggregated columns. Optionally chain ORDER BY and HAVING to sort or filter grouped results.

Can I GROUP BY multiple columns?

Yes. Provide a comma-separated list: GROUP BY column1, column2. PostgreSQL forms a distinct group for every unique combination of those columns.

How to filter grouped rows with HAVING?

HAVING applies conditions after grouping. Use it to keep or discard groups based on aggregate values (e.g., HAVING COUNT(*) > 10).

Practical GROUP BY example with ecommerce tables

SELECT c.id, c.name, COUNT(o.id) AS total_orders, SUM(o.total_amount) AS lifetime_value FROM Customers AS c JOIN Orders AS o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY lifetime_value DESC;

This query shows how many orders each customer placed and their total spend, ranked from highest to lowest.

Best practices for GROUP BY queries

1. Index grouping columns to accelerate grouping. 2. Avoid SELECT *; list only needed columns and aggregates. 3. Use integer or enum columns instead of large text fields when possible to reduce sort cost.

Common mistakes and how to avoid them

Missing columns in GROUP BY: Any non-aggregated column in SELECT must be in GROUP BY. Fix by adding the column or wrapping it in an aggregate.

Using WHERE instead of HAVING for aggregates: WHERE filters rows before grouping, so conditions on aggregates belong in HAVING.

FAQ

Is GROUP BY slower than raw aggregates?

Grouping introduces sorting or hashing overhead, but proper indexing and limiting columns keep performance acceptable.

Can I GROUP BY an expression?

Yes. Expressions or functions (e.g., DATE_TRUNC('month', order_date)) are allowed; just repeat the same expression in SELECT or use column aliases in PostgreSQL 9.6+.

Why How to Use GROUP BY in PostgreSQL is important

How to Use GROUP BY in PostgreSQL Example Usage


-- Average order value per product category
SELECT p.name,
       AVG(o.total_amount) AS avg_order_value
FROM   Products     AS p
JOIN   OrderItems   AS oi ON oi.product_id = p.id
JOIN   Orders       AS o  ON o.id = oi.order_id
GROUP  BY p.name
ORDER  BY avg_order_value DESC;

How to Use GROUP BY in PostgreSQL Syntax


SELECT column1, AGG_FUNC(column2)
FROM   table_name
[JOIN ...]
WHERE  conditions
GROUP  BY column1 [, columnN]
HAVING aggregate_condition
ORDER  BY column1 [, columnN];

-- Ecommerce example grouping monthly revenue
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total_amount)               AS revenue
FROM   Orders
GROUP  BY month
ORDER  BY month;

Common Mistakes

Selecting columns not listed in GROUP BY. PostgreSQL throws "column must appear in the GROUP BY clause". Fix by adding the column to GROUP BY or wrapping it in an aggregate.
Placing aggregate conditions in WHERE instead of HAVING. WHERE runs before grouping, so the aggregate isn’t available. Move the condition to HAVING.