GROUP BY single and multiple columns in PostgreSQL

Introduction

GROUP BY helps you organize data into groups so you can summarize or count things easily.

When you want to count how many sales each product has.

When you want to find the average score for each student.

When you want to see total expenses by category and month.

When you want to group customers by city and state to see how many are in each area.

Syntax

PostgreSQL

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

You list the columns you want to group by after GROUP BY.

Aggregate functions like COUNT(), SUM(), AVG() work on each group.

Examples

Groups products by category and counts how many products are in each category.

PostgreSQL

SELECT category, COUNT(*)
FROM products
GROUP BY category;

Groups sales data by city and state, then finds the average sales for each group.

PostgreSQL

SELECT city, state, AVG(sales)
FROM sales_data
GROUP BY city, state;

Counts employees in each department.

PostgreSQL

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;

Sample Program

This creates a sales table, adds some products with categories and amounts, then groups by category to find total sales amount per category.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product VARCHAR(50),
  category VARCHAR(50),
  amount INT
);

INSERT INTO sales (product, category, amount) VALUES
('Pen', 'Stationery', 10),
('Pencil', 'Stationery', 5),
('Notebook', 'Stationery', 15),
('Apple', 'Fruit', 7),
('Banana', 'Fruit', 3);

SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category;

Important Notes

Every column in SELECT that is not inside an aggregate function must be listed in GROUP BY.

You can group by one or many columns to get more detailed groups.

Summary

GROUP BY groups rows that have the same values in specified columns.

Use aggregate functions to summarize data in each group.

You can group by one or multiple columns depending on how you want to organize data.