Aggregate functions: COUNT, SUM, AVG, MIN, MAX

A teacher doesn't need every student's score — she needs the class average. A shop owner wants the total sales, not a look at every single order. The answer to such questions is not a list of rows but one number. In SQL these numbers are calculated by aggregate functions.

What is an aggregate function?

COUNT: counting

COUNT has three forms. COUNT(*) counts all rows. COUNT(email) counts only the rows that have a value in email — two students have a NULL email, so the result is 10. COUNT(DISTINCT city) gives the number of different cities.

SELECT COUNT(*)             AS students,
       COUNT(email)         AS with_email,
       COUNT(DISTINCT city) AS cities
FROM students;

students | with_email | cities
12 | 10 | 7

SUM, AVG, MIN and MAX

You can put several aggregates in one query. An average is often a long decimal (here 293.558), so we round it with ROUND. The result is always a single row, because the whole table is summarised as one group.

SELECT SUM(stock)            AS total_items,
       ROUND(AVG(price), 2)  AS avg_price,
       MIN(price)            AS cheapest,
       MAX(price)            AS most_expensive
FROM products;

total_items | avg_price | cheapest | most_expensive
1086 | 293.56 | 3.2 | 1450

MIN and MAX work not only with numbers but also with text and dates. For dates in the YYYY-MM-DD format, MIN gives the earliest date and MAX the latest.

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

first_order | last_order
2025-01-15 | 2025-07-07

Calculating with aggregates

The result of an aggregate is an ordinary number, so you can calculate with it. MAX(age) - MIN(age) gives the difference between the oldest and the youngest age. To find the percentage of students who have an email, divide COUNT(email) by COUNT(*) and multiply by 100. Careful: 100 * 10 / 12 is integer division and gives 83; for the exact result we write 100.0.

SELECT MAX(age) - MIN(age) AS age_range,
       100 * COUNT(email) / COUNT(*) AS int_pct,
       ROUND(100.0 * COUNT(email) / COUNT(*), 1) AS pct_with_email
FROM students;

age_range | int_pct | pct_with_email
3 | 83 | 83.3

Aggregates and WHERE

WHERE works before the aggregate: first the rows are filtered, then the calculation runs only on the rows that are left. Below we take only the enrollments of course 1 (Algebra): 4 students, average score 87.3.

SELECT COUNT(*)             AS enrollments,
       ROUND(AVG(score), 1) AS avg_score,
       MIN(score)           AS min_score,
       MAX(score)           AS max_score
FROM enrollments
WHERE course_id = 1;

enrollments | avg_score | min_score | max_score
4 | 87.3 | 75 | 97

SELECT SUM(price * stock) AS warehouse_value
FROM products;

warehouse_value
40260.6

SELECT AVG(age) AS avg_age
FROM students;

avg_age
15.5

SELECT COUNT(*) AS students
       -- add avg_score and best
FROM enrollments
WHERE course_id = 1;

students | avg_score | best
4 | 87.3 | 97

SELECT
  -- three aggregates here
FROM products
WHERE category = 'Electronics';

products | total_stock | cheapest
4 | 63 | 120.5