MySQL Aggregate Function

Last updated: October 19, 2026Oct 19, 2026 4 min read

An aggregate function takes many rows and returns one value: a count, a total, an average, the smallest or largest value. They power every summary report, from "how many orders today?" to "average score per class". This page introduces all of them; the following tutorials go deeper into each.

1. What Aggregates Do

Table: results (9 rows)

2. The Aggregate Functions

3. Aggregating a Whole Table

SELECT COUNT(*)      AS rows_total,
       COUNT(score)  AS scores_recorded,
       SUM(score)    AS total,
       ROUND(AVG(score), 2) AS average,
       MIN(score)    AS lowest,
       MAX(score)    AS highest
FROM results;

Result: 1 row

4. How Aggregates Treat NULL

• COUNT(*) counts rows, including rows with NULLs.
• All other aggregates ignore NULL values (Bhanu's missing Maths score above).
• On an empty set, COUNT returns 0 and the others return NULL.

SELECT COUNT(*) AS n, SUM(score) AS total, AVG(score) AS avg_score, MAX(score) AS top
FROM results WHERE subject = 'History';

Result: 1 row

SELECT COALESCE(SUM(score), 0) AS total_or_zero FROM results WHERE subject = 'History';

Result: 1 row

5. Aggregates per Group

SELECT class, subject, COUNT(score) AS students, ROUND(AVG(score), 1) AS avg_score, MAX(score) AS best
FROM results
GROUP BY class, subject
ORDER BY class, subject;

Result: 4 rows

See GROUP BY and HAVING.

6. DISTINCT Inside an Aggregate

SELECT COUNT(DISTINCT student) AS students, COUNT(DISTINCT class) AS classes, SUM(DISTINCT score) AS sum_of_distinct_scores FROM results;

Result: 1 row

7. GROUP_CONCAT and JSON Aggregates

SELECT class,
       GROUP_CONCAT(DISTINCT student ORDER BY student SEPARATOR ', ') AS students,
       JSON_ARRAYAGG(score) AS all_scores
FROM results
GROUP BY class;

Result: 2 rows

8. Statistical Aggregates

SELECT subject,
       ROUND(AVG(score), 2)        AS mean,
       ROUND(STDDEV_POP(score), 2) AS std_dev,
       ROUND(VAR_POP(score), 2)    AS variance
FROM results
GROUP BY subject;

Result: 2 rows

9. Aggregates as Window Functions

Add OVER (…) and an aggregate no longer collapses rows: each row keeps its details plus the group's value. Compare each score with its class average:

SELECT student, class, subject, score,
       ROUND(AVG(score) OVER (PARTITION BY class, subject), 1) AS class_avg,
       score - ROUND(AVG(score) OVER (PARTITION BY class, subject), 1) AS diff
FROM results
WHERE subject = 'Maths'
ORDER BY class, score DESC;

Result: 5 rows

10. Rules to Remember

• You can't use an aggregate in WHERE; use HAVING for conditions on aggregates.
• Every non-aggregated column in SELECT must be in GROUP BY.
• Aggregates can't be nested directly (MAX(AVG(x))); use a sub-query or CTE.
• Integer SUM returns DECIMAL; AVG of integers returns DECIMAL with 4 extra decimal places.

SELECT MAX(avg_score) AS best_class_average
FROM (SELECT class, AVG(score) AS avg_score FROM results GROUP BY class) t;

Result: 1 row

11. Summary

• Aggregates (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, JSON_ARRAYAGG, STDDEV…) turn many rows into one value.
• They ignore NULLs, except COUNT(*).
• Combine with GROUP BY for per-group results and HAVING to filter groups.
• Use DISTINCT inside to aggregate unique values; add OVER() to keep row details.