PostgreSQL GROUP BY Explained for Beginners (Examples)

PostgreSQL GROUP BY lets you split rows into groups and then count, add up, or average each group. If you want to know how many orders each customer made, or how much each product earned, GROUP BY is the tool. In this beginner guide you will learn what GROUP BY does in PostgreSQL, how to use it with COUNT, SUM and AVG, and the one mistake almost everyone makes at first.

You do not need any experience. Every term is explained the first time it appears, and every example can be copied into psql (the PostgreSQL command-line tool) or any other PostgreSQL client.

What does GROUP BY do in PostgreSQL?

A normal SELECT query returns one result row for every row in the table. GROUP BY changes that. It puts rows that share the same value into one bucket, called a group, and returns one result row per group.

Think about a box of coloured pencils. If you sort them into piles by colour, you get one pile per colour. GROUP BY sorts table rows into piles by a column of your choice. Then you can ask a question about each pile, such as how many pencils are in it.

The questions are answered by aggregate functions. An aggregate function takes many values and returns one single value. The most common ones are in this table.

Set up a small practice table

Let us create a table called orders and add a few rows. If you have not made a table before, our guide on creating tables is linked further down.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer TEXT NOT NULL,
  product TEXT NOT NULL,
  amount NUMERIC(8,2) NOT NULL
);

INSERT INTO orders (customer, product, amount) VALUES
  ('Anna', 'Keyboard', 40.00),
  ('Anna', 'Mouse', 15.50),
  ('Ben', 'Keyboard', 40.00),
  ('Ben', 'Monitor', 180.00),
  ('Ben', 'Mouse', 15.50),
  ('Chloe', 'Monitor', 180.00);

Line by line: CREATE TABLE makes a new table named orders. The id column is a number that PostgreSQL fills in for you (SERIAL) and it identifies each row (PRIMARY KEY). The customer and product columns hold text. The amount column holds a number with up to 8 digits and 2 after the decimal point (NUMERIC(8,2)). NOT NULL means the value can never be empty. INSERT INTO adds six rows, one per order.

Step 1: GROUP BY with COUNT

Let us answer the question: how many orders did each customer make?

SELECT customer, COUNT(*) AS total_orders
FROM orders
GROUP BY customer;

SELECT customer says we want to see the customer name. COUNT(*) counts the rows in each group. AS total_orders gives that count a friendly column name. FROM orders names the table. GROUP BY customer makes one group per customer. The result is Anna 2, Ben 3 and Chloe 1 (the row order may vary).

Step 2: GROUP BY with SUM

Now let us find how much money each customer spent in total.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

The structure is the same. Only the function changed. SUM(amount) adds up the amount column inside each group. Anna gets 55.50, Ben gets 235.50 and Chloe gets 180.00.

Step 3: GROUP BY with AVG, MIN and MAX

You can use several aggregate functions in one query. This gives the full picture for each product.

SELECT product,
       COUNT(*) AS times_sold,
       AVG(amount) AS average_amount,
       MAX(amount) AS biggest_sale
FROM orders
GROUP BY product;

Here we group by product instead of customer. For each product we count the sales, find the average amount, and find the biggest amount. Because a keyboard always costs 40.00 in our data, its average and biggest sale are both 40.00.

Tip: AVG often returns many decimal places. Wrap it in ROUND to tidy it up, for example ROUND(AVG(amount), 2).

The most common GROUP BY mistake

Beginners often write a query like this and get an error.

SELECT customer, product, COUNT(*)
FROM orders
GROUP BY customer;

PostgreSQL replies with: column "orders.product" must appear in the GROUP BY clause or be used in an aggregate function. The reason is simple. Anna has two products, but the result has only one row for Anna. PostgreSQL cannot decide which product to show.

The rule to remember: every column in SELECT must either be listed in GROUP BY or be inside an aggregate function like COUNT or SUM. You can fix the query in two ways.

-- Fix 1: group by both columns
SELECT customer, product, COUNT(*)
FROM orders
GROUP BY customer, product;

-- Fix 2: remove product from SELECT
SELECT customer, COUNT(*)
FROM orders
GROUP BY customer;

Fix 1 makes one group for each customer and product pair. Fix 2 removes the column that caused the problem. Pick the one that matches the question you are asking.

Step 4: Filter rows with WHERE before grouping

Use WHERE when you want to ignore some rows before they are put in groups. For example, let us only look at orders above 20.

SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE amount > 20
GROUP BY customer;

PostgreSQL first throws away every row where amount is 20 or less. Then it groups what is left and adds it up. The order is always: WHERE first, then GROUP BY.

Step 5: Filter groups with HAVING

What if you want to filter the groups themselves, such as customers who spent more than 100? WHERE cannot do this, because it runs before the groups exist. Use HAVING instead.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100;

PostgreSQL builds the groups, adds up each one, and then keeps only the groups where the total is above 100. Ben (235.50) and Chloe (180.00) stay. Anna (55.50) is removed.

Step 6: Sort the grouped results

Finally, add ORDER BY to show the biggest spenders first.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;

ORDER BY total_spent DESC sorts the result from the highest total to the lowest. DESC means descending. Leave it out, or use ASC, for the smallest first.

Query order cheat sheet

SQL clauses must be written in a fixed order. This is the order for a full grouped query.

1. SELECT: which columns and aggregates to show
2. FROM: which table to read
3. WHERE: filter single rows before grouping
4. GROUP BY: make the groups
5. HAVING: filter the groups
6. ORDER BY: sort the final result

Common beginner tips

• COUNT(*) counts all rows in a group. COUNT(column) skips rows where that column is empty (NULL).
• Use short, clear aliases with AS so your results are easy to read.
• Start with a simple GROUP BY on one column, check the result, then add more.
• If you get a 'must appear in the GROUP BY clause' error, check every column in SELECT.