Learn SQL  /  Aggregation  /  Aggregate Functions

Aggregate Functions

Aggregate functions collapse many rows down into a single summary value — COUNT, SUM, AVG, MIN, and MAX are the five you will use constantly.

COUNT — How Many Rows

R Code
SELECT COUNT(*) AS total_students
FROM students;
Output
total_students
4
💡 Tip
COUNT(*) counts all rows, including ones with NULL values. COUNT(column_name) counts only rows where that specific column is NOT NULL - the two can give different results on messy data.

SUM, AVG, MIN, MAX

R Code
SELECT
  SUM(score) AS total_score,
  AVG(score) AS avg_score,
  MIN(score) AS lowest,
  MAX(score) AS highest
FROM students;
Output
total_score | avg_score | lowest | highest
321         | 80.25     | 65     | 92

Aggregates Ignore NULL Values

R Code
-- if one score is NULL, AVG only divides by the non-NULL rows
SELECT COUNT(*) AS all_rows,
       COUNT(score) AS rows_with_score,
       AVG(score) AS avg_ignoring_nulls
FROM students;

Rounding Aggregate Results

R Code
SELECT ROUND(AVG(score), 1) AS avg_score
FROM students;
Output
avg_score
80.3

Advanced: Combining Aggregates with a Filter

🚀 Advanced Example
WHERE filters rows before they reach the aggregate function - useful for computing a summary over just a subset of the data.
R Code
SELECT AVG(score) AS avg_score_class_a
FROM students
WHERE class = "A";
Output
avg_score_class_a
90.0

Key Points

  • COUNT(*) counts all rows; COUNT(column) counts only non-NULL values in that column.
  • SUM, AVG, MIN, and MAX all ignore NULL values automatically.
  • ROUND(expression, decimals) formats an aggregate result to a clean number of decimal places.
  • WHERE filters rows BEFORE they reach an aggregate function - to filter groups AFTER aggregation, use HAVING instead.