COUNT, SUM, AVG, MIN, MAX in PostgreSQL

Introduction

These functions help you quickly find totals, averages, and extremes in your data. They make it easy to understand big groups of numbers.

Counting how many customers bought a product.

Adding up all sales in a month.

Finding the average score on a test.

Getting the smallest or largest value in a list, like the cheapest or most expensive item.

Summarizing data to make decisions or reports.

Syntax

PostgreSQL

SELECT AGGREGATE_FUNCTION(column_name) FROM table_name;

-- AGGREGATE_FUNCTION can be COUNT, SUM, AVG, MIN, or MAX

COUNT counts rows or non-null values.

SUM, AVG, MIN, MAX work only on numeric or comparable columns.

Examples

Counts all rows in the orders table.

PostgreSQL

SELECT COUNT(*) FROM orders;

Adds up all the prices in the products table.

PostgreSQL

SELECT SUM(price) FROM products;

Finds the average score from the tests table.

PostgreSQL

SELECT AVG(score) FROM tests;

Finds the youngest and oldest user.

PostgreSQL

SELECT MIN(age), MAX(age) FROM users;

Sample Program

This example creates a sales table, adds some fruit sales, then uses all five functions to summarize the data.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product VARCHAR(50),
  quantity INT,
  price NUMERIC
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7);

SELECT COUNT(*) AS total_sales, SUM(quantity) AS total_quantity, AVG(price) AS average_price, MIN(price) AS cheapest_price, MAX(price) AS most_expensive_price FROM sales;

Important Notes

COUNT(*) counts all rows, even if some columns are NULL.

SUM and AVG ignore NULL values in the column.

MIN and MAX work on numbers, dates, and text (alphabetical order).

Summary

COUNT, SUM, AVG, MIN, MAX are easy ways to get quick info from many rows.

They help you see totals, averages, and extremes in your data.

Use them in SELECT queries to summarize your tables.