Why aggregation matters in PostgreSQL

Introduction

Aggregation helps us quickly find summary information from many rows of data. It turns lots of details into simple answers.

When you want to find the total sales made in a month.

When you need to count how many customers bought a product.

When you want to know the average rating of a product.

When you want to find the highest or lowest price in a list.

When you want to group data by categories and get summaries for each.

Syntax

PostgreSQL

SELECT AGGREGATE_FUNCTION(column_name) FROM table_name;

AGGREGATE_FUNCTION can be SUM, COUNT, AVG, MAX, MIN, etc.

You can add GROUP BY to get summaries per group.

Examples

Counts all rows in the orders table.

PostgreSQL

SELECT COUNT(*) FROM orders;

Finds the average price of all products.

PostgreSQL

SELECT AVG(price) FROM products;

Shows total quantity sold for each category.

PostgreSQL

SELECT category, SUM(quantity) FROM sales GROUP BY category;

Sample Program

This example creates a sales table, adds some data, and then sums the quantity sold per category.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  category TEXT,
  quantity INT
);

INSERT INTO sales (category, quantity) VALUES
('Books', 10),
('Books', 5),
('Toys', 7),
('Toys', 3),
('Clothes', 8);

SELECT category, SUM(quantity) AS total_quantity FROM sales GROUP BY category ORDER BY category;

Important Notes

Aggregation functions ignore NULL values by default.

Use GROUP BY to get summaries for each group instead of the whole table.

Summary

Aggregation turns many rows into simple summary numbers.

Common functions include COUNT, SUM, AVG, MAX, and MIN.

GROUP BY helps get summaries for each category or group.