Aggregation Updated November 2026

SUM, AVG, MIN, MAX

SUM(column) adds all values, AVG(column) returns their mean, MIN and MAX return the smallest and largest value (numbers, text or dates). All four ignore NULLs and, with GROUP BY, give one result per group: SELECT dept, AVG(salary) FROM employees GROUP BY dept.

Syntax

SUM(column) | AVG(column) | MIN(column) | MAX(column)

Examples

SELECT SUM(amount) AS revenue, AVG(amount) AS avg_order FROM orders WHERE status = 'paid';

Result: revenue | avg_order 48250.0 | 155.64

SELECT customer_id, SUM(amount) AS spent FROM orders GROUP BY customer_id ORDER BY spent DESC;

Result: Total spend per customer, biggest first

SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;

Result: first_order | last_order 2024-01-03 | 2025-06-28

MIN and MAX work on dates and text, not only numbers.

Common mistakes

AVG of an integer column in PostgreSQL returns a numeric with full precision, but integer division elsewhere (SUM(x) / COUNT(*)) truncates; multiply by 1.0 or cast to get decimals.
SUM over no rows returns NULL, not 0; wrap it as COALESCE(SUM(amount), 0) when a zero is expected.
Selecting MAX(salary) together with name to find the top earner returns the wrong name in SQLite and an error in PostgreSQL; use ORDER BY salary DESC LIMIT 1 or a subquery.

Practice SUM, AVG, MIN, MAX

These drills check your answer on live data, free, no account needed.

Spend per customerFor each customer_id, return the customer_id and the total amount of their paid orders. Customers without paid orders do not appear.
Average salary per departmentFor each dept_id, return the dept_id and the average salary. Leave out employees without a department.
First and last orderReturn the earliest and the latest order date, as two columns in one row.

Questions

How do I find the row with the maximum value?

Use ORDER BY column DESC LIMIT 1, or WHERE column = (SELECT MAX(column) FROM table) to also get ties. Per group, use a window function such as ROW_NUMBER() OVER (PARTITION BY group ORDER BY column DESC).

Does AVG include NULL values?

No. NULLs are skipped in both the sum and the count, so AVG(x) equals SUM(x) / COUNT(x), not SUM(x) / COUNT(*).

Can I SUM a boolean or a condition?

Yes with CASE: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) sums only paid orders. PostgreSQL also supports SUM(amount) FILTER (WHERE status = 'paid').

GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
HAVINGHAVING filters the groups made by GROUP BY using aggregate conditions such as COUNT(*) > 1 or SUM(amount) > 1000. Examples, HAVING vs WHERE, both together.
CASE WHENCASE WHEN returns different values depending on conditions. Label rows, bucket numbers into ranges, pivot with SUM(CASE ...), sort custom orders, handle NULL.