MySQL aggregate functions

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

Every query so far returned rows. An aggregate function reads many rows and returns 1 value: how many, the total, the average, the largest. It is the first thing you reach for when the question is about a set rather than a row. Open the client with mysql -u root -p sakila before you start.

How an aggregate function works

SELECT AGGREGATE_FUNCTION(COLUMN_NAME)
FROM TABLE_NAME;

Replace AGGREGATE_FUNCTION with one of the functions below, COLUMN_NAME with the column to read, and TABLE_NAME with the table.

Function	Returns
`COUNT(*)`	how many rows there are, NULLs included
`COUNT(column)`	how many rows have a value in that column
`SUM(column)`	the total
`AVG(column)`	the mean
`MIN(column)`	the smallest value
`MAX(column)`	the largest value

With no GROUP BY, the whole table is 1 group, so the result is always exactly 1 row. GROUP BY is how you get 1 row per category instead.

Examples

1) Count the rows

SELECT COUNT(*) AS films FROM film;

+-------+
| films |
+-------+
|  1000 |
+-------+
1 row in set (0.00 sec)

1 row, whatever the table holds. An aggregate always answers.

2) Several aggregates at once

SELECT MIN(length) AS shortest, MAX(length) AS longest, AVG(length) AS mean
FROM film;

+----------+---------+----------+
| shortest | longest | mean     |
+----------+---------+----------+
|       46 |     185 | 115.2720 |
+----------+---------+----------+
1 row in set (0.00 sec)

Each one reads the same 1,000 rows. Name them with aliases, because the default heading is the whole expression.

3) Total a money column

SELECT SUM(amount) AS takings, COUNT(*) AS payments FROM payment;

+----------+----------+
| takings  | payments |
+----------+----------+
| 67406.56 |    16044 |
+----------+----------+
1 row in set (0.00 sec)

COUNT(*) and COUNT(column) are different questions

COUNT(*) counts rows. COUNT(column) counts the rows where that column holds a value, so it skips NULL.

SELECT COUNT(*) AS rows_total, COUNT(address2) AS with_a_value
FROM address;

+------------+--------------+
| rows_total | with_a_value |
+------------+--------------+
|        603 |          599 |
+------------+--------------+
1 row in set (0.00 sec)

603 addresses, and 4 of them have no second line at all. The other 599 hold an empty string, which COUNT treats as a value like any other: it is looking for NULL, not for blankness. The IS NULL lesson covers that difference.

Every aggregate skips NULL

The rule is not special to COUNT. No aggregate looks at a NULL, which means a column with nothing in it produces nothing to average. Averaging an id is meaningless arithmetic, and this query does it only because original_language_id is the one column in Sakila that is NULL in every row.

SELECT COUNT(*) AS films,
       COUNT(original_language_id) AS with_a_value,
       AVG(original_language_id) AS mean
FROM film;

+-------+--------------+------+
| films | with_a_value | mean |
+-------+--------------+------+
|  1000 |            0 | NULL |
+-------+--------------+------+
1 row in set (0.00 sec)

AVG returned NULL rather than 0, because there was nothing to divide. That matters most for AVG on a column that is only sometimes filled in: the mean is over the rows that have a value, not over all the rows, and the 2 numbers are not the same. SUM of nothing is NULL too, while COUNT of nothing is 0.

Summary

Use an aggregate to reduce many rows to 1 value.
Expect exactly 1 row back when there is no GROUP BY.
Read COUNT(*) as how many rows and COUNT(column) as how many have a value.
Expect every aggregate to skip NULL, so an average is over the rows that have a value.
Expect NULL rather than 0 from SUM and AVG when nothing qualifies.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How an aggregate function works

Examples

1) Count the rows

2) Several aggregates at once

3) Total a money column

COUNT(*) and COUNT(column) are different questions

Every aggregate skips NULL

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How an aggregate function works

​Examples

​1) Count the rows

​2) Several aggregates at once

​3) Total a money column

​COUNT(*) and COUNT(column) are different questions

​Every aggregate skips NULL

​Summary

​See also

How an aggregate function works

Examples

1) Count the rows

2) Several aggregates at once

3) Total a money column

COUNT(*) and COUNT(column) are different questions

Every aggregate skips NULL

Summary

See also