beginner 7 min read

Aggregate Functions in SQL

Written with a little help from AI, reviewed and edited by a human.

So far we’ve learned how to get individual rows from a table. But sometimes you don’t want individual rows — you want a summary of the data. Like “how many students are there?” or “what’s the average score?” or “who got the highest grade?”

That’s what aggregate functions do. They take many rows and turn them into one answer.

Real life example

Before we look at any SQL, let’s think about this with a real life scenario.

Imagine you’re a teacher and you just finished grading a math test. You have a stack of papers with each student’s score.

StudentScore
Alice85
Bob92
Charlie78
Diana88
Eve92

Now the principal comes in and asks:

  • “How many students took the test?” → You count the papers. That’s 5 students.
  • “What was the highest score?” → You look through and find 92.
  • “What was the average score?” → You add them all up (85+92+78+88+92 = 435) and divide by 5. That’s 87.
  • “What’s the total of all scores combined?” → You just did that: 435.

You didn’t list every single student — you gave a summary of the results. That’s exactly what aggregate functions do in SQL.

The aggregate functions

SQL has five main aggregate functions:

  • COUNT() — Counts how many rows match
  • SUM() — Adds up all the values in a column
  • AVG() — Calculates the average of all values
  • MAX() — Finds the highest value
  • MIN() — Finds the lowest value

Let’s use the same student scores from above in a table called scores:

idstudentscore
1Alice85
2Bob92
3Charlie78
4Diana88
5Eve92

COUNT

COUNT() tells you how many rows there are.

SELECT COUNT(*) FROM scores;

Result:

COUNT(*)
5

This counts all rows in the table. There are 5 students, so we get 5.

You can also count a specific column:

SELECT COUNT(student) FROM scores;

Same result: 5. But there’s a difference: COUNT(*) counts all rows, even if some columns are empty. COUNT(column) only counts rows where that column has a value (not NULL).

MAX and MIN

MAX() gives you the highest value. MIN() gives you the lowest.

SELECT MAX(score) FROM scores;

Result:

MAX(score)
92
SELECT MIN(score) FROM scores;

Result:

MIN(score)
78

The highest score is 92 (Bob and Eve both got it). The lowest is 78 (Charlie).

SUM

SUM() adds up all the values in a column.

SELECT SUM(score) FROM scores;

Result:

SUM(score)
435

It adds 85 + 92 + 78 + 88 + 92 = 435.

AVG

AVG() calculates the average (mean) of all values.

SELECT AVG(score) FROM scores;

Result:

AVG(score)
87

It takes the sum (435) and divides by the count (5) = 87.

Using multiple aggregates at once

You can use multiple aggregate functions in the same query:

SELECT
  COUNT(*) AS total_students,
  AVG(score) AS average_score,
  MAX(score) AS highest_score,
  MIN(score) AS lowest_score
FROM scores;

Result:

total_studentsaverage_scorehighest_scorelowest_score
5879278

Now you have a full summary in one go. Notice we used AS to give each result a friendly name — this is called an alias and it makes the output easier to read.

GROUP BY — aggregating subgroups

So far we’ve been getting one summary for the entire table. But what if you want to group the data and get a summary for each group?

Let’s say we have a bigger table where students took multiple tests:

idstudentsubjectscore
1AliceMath85
2AliceEnglish90
3BobMath92
4BobEnglish88
5CharlieMath78
6CharlieEnglish82

Now let’s say we want to know each student’s average score.

We can’t just do SELECT AVG(score) — that would give us the average of all scores combined. We want the average per student.

This is where GROUP BY comes in:

SELECT student, AVG(score) AS average_score
FROM scores
GROUP BY student;

Result:

studentaverage_score
Alice87.5
Bob90
Charlie80

Here’s what happened:

  1. SQL grouped all rows by the student column
  2. For each group (each student), it calculated the average score
  3. It returned one row per group

Think of it like this: imagine you sort the papers into piles — one pile for Alice, one for Bob, one for Charlie. Then you calculate the average for each pile separately.

Real life GROUP BY example

Back to the teacher scenario. The principal now asks: “What’s each student’s average score across all their tests?”

Instead of looking at all papers at once, you:

  1. Make a pile for each student (Alice’s tests in one pile, Bob’s in another, etc.)
  2. Calculate the average for each pile
  3. Write down the results:
    • Alice: 87.5
    • Bob: 90
    • Charlie: 80

That’s exactly what GROUP BY does — it creates “piles” (groups) and then applies the aggregate function to each pile.

Using COUNT with GROUP BY

You can also count how many rows are in each group:

SELECT student, COUNT(*) AS test_count
FROM scores
GROUP BY student;

Result:

studenttest_count
Alice2
Bob2
Charlie2

This tells you how many tests each student took.

Filtering groups with HAVING

Sometimes you want to filter the results after grouping. For example, “show me only students whose average score is above 85”.

You might think to use WHERE, but WHERE filters rows before grouping. To filter after grouping, you use HAVING:

SELECT student, AVG(score) AS average_score
FROM scores
GROUP BY student
HAVING AVG(score) > 85;

Result:

studentaverage_score
Alice87.5
Bob90

Charlie is gone because his average (80) is not above 85.

WHERE filters individual rows before grouping. HAVING filters groups after aggregating.

When to use what

  • Use COUNT when you need to know “how many?”
  • Use SUM when you need a total (like total sales, total points)
  • Use AVG when you need the average (like average age, average price)
  • Use MAX/MIN when you need the highest or lowest value
  • Use GROUP BY when you want to break data into groups and get a summary for each group
  • Use HAVING when you need to filter groups based on aggregate results

Aggregate functions turn many rows into one summary. They’re essential when you need to analyze data instead of just listing it.

Share: