Aggregate Functions in MySQL

What Aggregation Does

Aggregate functions answer summary questions: How many students? What is the class average? Which mark is highest? Without GROUP BY, the filtered input is one group and the query normally returns one summary row.

SELECT COUNT(*) AS students,
       SUM(marks) AS total_marks,
       ROUND(AVG(marks), 2) AS average_marks,
       MIN(marks) AS lowest,
       MAX(marks) AS highest
FROM students;

The Five Core Aggregates

NULL, Empty Inputs and Result Types

Most aggregates ignore NULL, which means AVG does not automatically treat missing marks as zero. COUNT(*) still counts the row. If a filter produces no input rows, COUNT returns 0 while SUM/AVG/MIN/MAX usually return NULL.

SELECT COUNT(*) AS rows_found,
       COUNT(marks) AS known_marks,
       COALESCE(SUM(marks), 0) AS safe_total
FROM students
WHERE class_name = 'XII-Z';

Use COALESCE only where a zero total is semantically correct. Exact result precision depends on input types; use DECIMAL for exact marks and money.

Filter Rows Before, Groups After

SELECT COUNT(*) AS active_students,
       ROUND(AVG(marks), 2) AS active_average
FROM students
WHERE status = 'Active';

WHERE removes inactive rows before aggregation. HAVING filters completed groups and is explained in the next lesson.

Whole Table vs Grouped Report

SELECT class_name,
       COUNT(*) AS students,
       ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY class_name
ORDER BY class_name;

Every selected nonaggregate column must be valid for the group. With ONLY_FULL_GROUP_BY, MySQL rejects ambiguous selected columns that are neither aggregated nor functionally dependent on GROUP BY columns.

Mistakes and Practice

• Confusing COUNT(*) with COUNT(nullable_column).
• Using aggregate conditions in WHERE.
• Replacing NULL with zero without a business rule.
• Selecting unrelated columns beside aggregates.
• Rounding too early before totals or averages are complete.

1. Calculate active total and average.
2. Count known marks separately from rows.
3. Return one summary per status.
4. Predict results after inserting a NULL mark.

Quick Summary

• Aggregates summarize rows into one result per group.
• COUNT, SUM, AVG, MIN and MAX have distinct NULL behavior.
• WHERE filters input rows; GROUP BY creates groups; HAVING filters groups.
• Choose correct numeric types and round only for presentation.

Official References

• Aggregate Function Descriptions — MySQL 8.4
• MySQL Handling of GROUP BY
• Working with NULL Values

References reviewed 14 August 2026. Outputs use the stated four-row dataset.