Databases & SQL · Class 12 · Aggregate Functions & Grouping

MySQLaggregates⏱️ 9 min read

MAX, MIN, SUM & AVG

Every query so far has answered “which rows?”. These answer a different kind of question: the highest salary, the total paid out, the class average. Many rows go in and one value comes out.

1Many rows in, one value out

The examples here use the teachers table — twelve teachers, with a sal column. Note that one of them, Nirmala Devi, has NULL for salary; that becomes important very quickly.

max()

The largest value

min()

The smallest value

sum()

Everything added up

avg()

The mean

2The four, one at a time

MySQL command line client

mysql> select sum(sal) from teachers;

+----------+

| sum(sal) |

+----------+

| 515000 |

+----------+

1 row in set (0.00 sec)

mysql> select max(sal) from teachers;

+----------+

| max(sal) |

+----------+

| 62000 |

+----------+

1 row in set (0.01 sec)

mysql> select min(sal) from teachers;

+----------+

| min(sal) |

+----------+

| 31500 |

+----------+

1 row in set (0.00 sec)

Each one returns a resultset of exactly one row and one column. That is worth noticing: the answer is still a table, because everything MySQL returns is a table.

The heading is the whole expression

The column comes back headed sum(sal), because a calculated column has no name of its own. This is precisely the situation the alias lesson was for: select sum(sal) as "Total Salary" gives it a readable heading.

3They all ignore NULL

This is the single most examinable fact in the lesson. Twelve teachers, but only eleven have a salary recorded:

MySQL command line client

mysql> select avg(sal) from teachers;

+--------------------+

| avg(sal) |

+--------------------+

| 46818.181818181816 |

+--------------------+

1 row in set (0.00 sec)

The total is 515000 and the average is 46818.18. Divide: 515000 ÷ 46818.18 gives 11, not 12. MySQL added up eleven salaries and divided by eleven — the NULL row was left out of both the sum and the count.

Skipped, not counted as zero

Had the missing salary been treated as 0, the average would have been 515000 ÷ 12 = 42916.67 — noticeably lower, and wrong. An unknown salary is not a salary of nothing, so it is excluded. This is why the concepts chapter insisted that NULL is not zero.

4Why the average looks like that

46818.181818181816 is an awkward thing to put in a report. The sal column is a float, and floats store fractions in binary, so a value that is not exact in binary comes back with a long tail. It is not an error and not a MySQL quirk — it is how floating-point numbers work everywhere, Python included.

The fix is to round the answer to as many places as you actually want:

round() wrapped around avg() — grouping is the next lesson but one

mysql> select round(avg(percent), 2), city from students group by city;

+------------------------+----------+

| round(avg(percent), 2) | city |

+------------------------+----------+

| 84.5 | Singtam |

| 87.38 | Gangtok |

| 62.7 | NULL |

| 95.2 | Namchi |

| 76.25 | Kolkata |

| 72.8 | Siliguri |

+------------------------+----------+

6 rows in set (0.00 sec)

For Computer Science students

round() is in the Informatics Practices syllabus rather than the Computer Science one, and it gets a full lesson later in this track. It is used here only to tidy a number; you will never be marked down for leaving an average unrounded.

5Aggregates work on whatever the where left behind

An aggregate is applied after the rows have been filtered. So select avg(percent) from students where grade = 12; averages the grade-12 students only. The order is always the same: pick the rows, then aggregate what is left.

The four functions at a glance

Function	Returns	Works on	NULLs
max(col)	The largest value	Numbers, dates, text	Ignored
min(col)	The smallest value	Numbers, dates, text	Ignored
sum(col)	The total	Numbers only	Ignored
avg(col)	The mean	Numbers only	Ignored

max and min are not just for numbers

max(dob) gives the latest date — the youngest student. min(name) gives the name that comes first alphabetically. Anything that can be sorted has a largest and a smallest.