> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL aggregate functions > How COUNT, SUM, AVG, MIN and MAX reduce many rows to 1 value in MySQL, why every one of them skips NULL, and what separates COUNT(*) from COUNT(column). 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 ```sql theme={null} 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](/docs/tutorial/group-by) is how you get 1 row per category instead. ## Examples ### 1) Count the rows ```sql theme={null} SELECT COUNT(*) AS films FROM film; ``` ```text theme={null} +-------+ | films | +-------+ | 1000 | +-------+ 1 row in set (0.00 sec) ``` 1 row, whatever the table holds. An aggregate always answers. ### 2) Several aggregates at once ```sql theme={null} SELECT MIN(length) AS shortest, MAX(length) AS longest, AVG(length) AS mean FROM film; ``` ```text theme={null} +----------+---------+----------+ | 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](/docs/tutorial/column-aliases), because the default heading is the whole expression. ### 3) Total a money column ```sql theme={null} SELECT SUM(amount) AS takings, COUNT(*) AS payments FROM payment; ``` ```text theme={null} +----------+----------+ | 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. ```sql theme={null} SELECT COUNT(*) AS rows_total, COUNT(address2) AS with_a_value FROM address; ``` ```text theme={null} +------------+--------------+ | 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](/docs/tutorial/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. ```sql theme={null} SELECT COUNT(*) AS films, COUNT(original_language_id) AS with_a_value, AVG(original_language_id) AS mean FROM film; ``` ```text theme={null} +-------+--------------+------+ | 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. ## See also * [GROUP BY](/docs/tutorial/group-by) — 1 result row per category instead of 1 in total * [COUNT DISTINCT](/docs/tutorial/count-distinct) — counting different values rather than rows * [IS NULL](/docs/tutorial/is-null) — the values these functions skip * [MySQL GROUP BY performance](/docs/guides/group-by-having) — what aggregating costs at scale