Aggregate Functions

Aggregate functions take multiple rows and condense them into a single result. They are the backbone of data analysis in SQL. When you want to count, sum, average, or find extremes across your data, aggregate functions are the tool.

COUNT

Count rows:

-- Count all students
SELECT COUNT(*) FROM students;

-- Count students with an email
SELECT COUNT(email) FROM students;

-- Count unique last names
SELECT COUNT(DISTINCT last_name) FROM students;

COUNT(*) counts all rows including those with NULL values. COUNT(column) only counts non-NULL values.

SUM and AVG

Calculate totals and averages:

-- Total stock across all products
SELECT SUM(stock) AS total_stock FROM products;

-- Average price
SELECT AVG(price) AS avg_price FROM products;

-- Round the average
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

NULL values are ignored by SUM and AVG. If all values are NULL, the result is NULL.

MIN and MAX

Find the smallest and largest values:

-- Cheapest and most expensive products
SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

-- Earliest and latest enrollment
SELECT
  MIN(enrollment_date) AS earliest,
  MAX(enrollment_date) AS latest
FROM students;

GROUP BY

Aggregate functions become really powerful with GROUP BY. It groups rows that share a value and applies the aggregate to each group:

-- Students per department
SELECT department_id, COUNT(*) AS student_count
FROM students
GROUP BY department_id;

-- Average grade per course
SELECT
  c.name,
  ROUND(AVG(CASE e.grade
    WHEN 'A' THEN 4 WHEN 'B' THEN 3
    WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0
  END), 2) AS avg_grade
FROM courses c
JOIN enrollments e ON c.id = e.course_id
GROUP BY c.id, c.name
ORDER BY avg_grade DESC;

HAVING

Use HAVING to filter groups (like WHERE but for aggregates):

-- Departments with more than 50 students
SELECT department_id, COUNT(*) AS student_count
FROM students
GROUP BY department_id
HAVING COUNT(*) > 50;

-- Courses with average grade above 3.0
SELECT c.name, AVG(grade_numeric) AS avg_grade
FROM courses c
JOIN enrollments e ON c.id = e.course_id
GROUP BY c.id, c.name
HAVING AVG(grade_numeric) > 3.0;

WHERE filters rows before grouping. HAVING filters groups after aggregation. Remember this distinction.

String Aggregation

PostgreSQL has special aggregate functions for strings:

-- List all students in a course as a comma-separated string
SELECT
  c.name,
  STRING_AGG(s.first_name || ' ' || s.last_name, ', ' ORDER BY s.last_name)
    AS students_list
FROM courses c
JOIN enrollments e ON c.id = e.course_id
JOIN students s ON e.student_id = s.id
GROUP BY c.id, c.name;

STRING_AGG concatenates values from multiple rows into a single string. Very handy for reports.

Array Aggregation

You can also aggregate values into an array:

SELECT
  department_id,
  ARRAY_AGG(first_name || ' ' || last_name) AS student_names
FROM students
GROUP BY department_id;

This creates an array of student names for each department. Useful when you need a collection of values rather than a single concatenated string.