COUNT, SUM, AVG, MIN and MAX

One Dataset, Exact Results

SELECT COUNT(*) AS students,
       SUM(marks) AS total,
       AVG(marks) AS average,
       MIN(marks) AS minimum,
       MAX(marks) AS maximum
FROM students;

The displayed scale of AVG may vary with client and type; the mathematical value is 85.

COUNT Variants

SELECT COUNT(*) AS all_rows,
       COUNT(marks) AS known_marks,
       COUNT(DISTINCT class_name) AS classes
FROM students;

COUNT(1) also counts rows, but COUNT(*) states intent clearly and MySQL optimizes it. COUNT(DISTINCT expression) counts distinct non-NULL values.

SUM and AVG

SELECT SUM(marks) AS active_total,
       ROUND(AVG(marks), 2) AS active_average
FROM students
WHERE status = 'Active';

AVG is SUM of non-NULL inputs divided by their non-NULL count. Do not round individual marks before aggregation unless that is the stated business rule.

MIN and MAX

SELECT MIN(marks) AS lowest,
       MAX(marks) AS highest,
       MIN(full_name) AS first_name,
       MAX(full_name) AS last_name
FROM students;

MIN/MAX work on comparable values; text results follow collation. To retrieve the student row holding the maximum, use ORDER BY marks DESC with LIMIT and a tie policy, or join to a subquery—MAX alone returns only the value.

Conditional Aggregation

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN marks >= 85 THEN 1 ELSE 0 END) AS marks_85_plus
FROM students;

Conditional aggregation creates several metrics in one scan and generalizes cleanly inside GROUP BY reports.

Mistakes and Practice

• Using COUNT(column) when NULL rows must count.
• Assuming MAX returns the whole winning row.
• Rounding each input too early.
• Converting text to numbers implicitly.
• Dividing SUM by COUNT(*) when NULL inputs should be excluded.

1. Calculate inactive count and average.
2. Count distinct statuses.
3. Return min/max per class.
4. Count marks at least 90 conditionally.

Quick Summary

• COUNT has row, non-NULL and distinct forms.
• SUM totals; AVG uses known values; MIN/MAX return extreme values.
• Conditional aggregation builds multiple report metrics.
• NULL, precision, collation and tie handling matter.

Official References

• Aggregate Function Descriptions — MySQL 8.4
• Precision Math
• Working with NULL

References reviewed 14 August 2026.