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

Aggregate Functions — summary numbers

Aggregate functions crunch many rows into one number. They ignore NULL values except COUNT(*) which counts all rows.

Use them to answer questions like 'What is the average score?' or 'How many students enrolled?'

Real-life example: Aggregates are like the summary row at the bottom of a report — total sales, average rating, highest score.

SELECT
  COUNT(*) AS total_rows,
  COUNT(score) AS scored_rows,
  MIN(score) AS lowest_score,
  MAX(score) AS highest_score,
  SUM(score) AS score_sum,
  AVG(score) AS average_score
FROM enrollments;

MIN — smallest value

MIN returns the lowest value in a column. Works on numbers, dates, and text (alphabetically first).

Real-life example: MIN is like finding the earliest exam date on the calendar.

SELECT MIN(score) AS lowest_score
FROM enrollments;

MAX — largest value

MAX returns the highest value. Pair MIN and MAX to see the range of scores in a class.

Real-life example: MAX is the top mark on the leaderboard — who scored highest?

SELECT MAX(score) AS top_score
FROM enrollments
WHERE course_id = 1;

COUNT — how many rows

COUNT(*) counts all rows. COUNT(column) counts rows where that column is not NULL.

Real-life example: COUNT is headcount at assembly — how many students are present?

SELECT COUNT(*) AS student_count FROM students;

SELECT COUNT(score) AS graded_enrollments
FROM enrollments;

SUM — total of numbers

SUM adds numeric values. Useful for total credits enrolled or total points across assignments.

Real-life example: SUM is like adding every bill amount at month end to get the total spend.

SELECT SUM(c.credits) AS total_credits
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 101;

AVG — average value

AVG computes the mean. It skips NULL scores automatically.

Real-life example: AVG is the class average printed on the report card header.

SELECT AVG(score) AS class_average
FROM enrollments
WHERE course_id = 2;