Aggregate Functions

Introduction

So far, every query has returned one row of output per row of input. But often you do not want individual rows — you want a summary. How many students are there? What is the average score? Who scored the highest?

Aggregate functions answer exactly these kinds of questions by summarizing data across many rows into a single value.

What Are Aggregate Functions?

An aggregate function takes many rows of data as input and returns a single summarizing value as output — a count, a total, an average, a minimum, or a maximum.

This is fundamentally different from the queries you have written so far, where every matching row appeared individually in the result.

COUNT, SUM, AVG, MIN, MAX

Consider a "students" table with a marks column. Each of the five aggregate functions can be applied to that column to answer a different question.

SELECT COUNT(*) AS total_students,
       SUM(marks) AS total_marks,
       AVG(marks) AS average_marks,
       MIN(marks) AS lowest_marks,
       MAX(marks) AS highest_marks
FROM students;

+-----------------+-------------+---------------+--------------+---------------+
| total_students  | total_marks | average_marks | lowest_marks | highest_marks |
+-----------------+-------------+---------------+--------------+---------------+
| 4               | 331         | 82.7500       | 70           | 91            |
+-----------------+-------------+---------------+--------------+---------------+

A single query just answered five different summary questions about the entire table at once, without you having to manually add up numbers yourself.

Worked Example

Suppose you specifically need the average score across all students, and the highest score achieved, for a class report.

SELECT AVG(marks) AS average_score, MAX(marks) AS highest_score
FROM students;

+---------------+----------------+
| average_score | highest_score  |
+---------------+----------------+
| 82.7500       | 91             |
+---------------+----------------+

The average across all four students is 82.75, and the highest single score is 91 — both computed directly by MySQL, without pulling all the raw rows back and calculating it yourself.

COUNT(*) vs COUNT(column)

COUNT(*) counts every row in the result, regardless of what is inside any particular column, including rows that contain NULL values. COUNT(column) counts only the rows where that specific column is not NULL.

This distinction matters. Suppose one student's marks have not been entered yet and are stored as NULL.

SELECT COUNT(*) AS all_rows, COUNT(marks) AS rows_with_marks
FROM students;

+-----------+------------------+
| all_rows  | rows_with_marks  |
+-----------+------------------+
| 4         | 3                |
+-----------+------------------+

COUNT(*) returned 4, counting every row including Neha's. COUNT(marks) returned 3, because it skips any row where marks is NULL.

Looking Ahead to GROUP BY

So far, every aggregate function has summarized the entire table into a single row. But what if you wanted the average marks per grade, or the count of students per class, instead of one grand total?

That is exactly what the GROUP BY clause, covered in the next lesson, unlocks — it lets you apply these same aggregate functions separately to different groups of rows within a single query, dramatically increasing their power.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Aggregate functions let you compute summary statistics — counts, totals, averages, minimums, and maximums — directly inside a query, instead of pulling raw rows back and calculating manually. COUNT(*) and COUNT(column) behave differently around NULL values, which is a subtle but important distinction.

In this lesson, you learned all five core aggregate functions and computed the average and highest score across the students table. Next, you will learn GROUP BY, which lets you apply these same functions to separate groups of rows within one query.