Chapter 15 of 35

Aggregate Functions

So far every query has returned rows from the table more or less as they are: filtered, sorted, de-duplicated, but still one output row per stored row. Real questions are often different. "How many employees do we have?" "What is our total sales figure?" "What is the average salary?" "When did we hire our first person?" Each of these wants one number calculated from many rows.

That is the job of aggregate functions. In this chapter we will learn the five core ones (COUNT, SUM, AVG, MIN and MAX), understand precisely how each one treats NULL (a favourite interview topic), see what happens on an empty table, and learn the two errors every beginner hits when mixing aggregates with normal columns. All examples use the shared company_db sample database with real MySQL 8.4 output.


What is an Aggregate Function?

An aggregate function takes the values of a column (or expression) from a whole set of rows and returns a single value that summarises them.

Compare a normal function with an aggregate:

  • ROUND(salary) is a scalar function. It works on one row at a time: 10 rows in, 10 values out.

  • AVG(salary) is an aggregate function. It looks at all the rows together: 10 rows in, 1 value out.

salary
---------
150000  \
 95000   |
 95000   |
110000   |
 62000   |   AVG(salary)  -->  82500
 58000   |   SUM(salary)  -->  825000
 70000   |   MAX(salary)  -->  150000
 48000   |   COUNT(*)     -->  10
 82000   |
 55000  /

The five standard aggregate functions:

Function

Returns

Works on

COUNT()

Number of rows or non-NULL values

Any type

SUM()

Total of the values

Numbers

AVG()

Average (mean) of the values

Numbers

MIN()

Smallest value

Numbers, strings, dates

MAX()

Largest value

Numbers, strings, dates

In this chapter we aggregate over the whole table (or the rows left after WHERE), so every query returns exactly one row. In the next chapter, GROUP BY & HAVING, we will compute the same functions per group, for example the average salary of each department.


COUNT: How Many?

COUNT comes in three forms, and the difference between them is one of the most asked SQL interview questions.

Form

Counts

COUNT(*)

All rows, whatever they contain

COUNT(column)

Rows where that column is not NULL

COUNT(DISTINCT column)

Number of different non-NULL values

SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

Now all three forms side by side on orders, where guest order 1008 has a NULL customer_id:

SELECT COUNT(*)                    AS total_rows,
       COUNT(customer_id)          AS with_customer,
       COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders;
+------------+---------------+--------------------+
| total_rows | with_customer | distinct_customers |
+------------+---------------+--------------------+
|          8 |             7 |                  4 |
+------------+---------------+--------------------+
1 row in set (0.00 sec)

Let's trace it over the 8 customer_id values:

101, 102, 101, 103, 104, 102, 101, NULL

COUNT(*)                     -> 8 rows
COUNT(customer_id)           -> 7    (the NULL is skipped)
COUNT(DISTINCT customer_id)  -> 4    (101, 102, 103, 104; NULL skipped)

Each answers a different business question: 8 orders were placed; 7 of them by registered customers; 4 different customers have ordered.

COUNT(column) gives a quick way to see how complete your data is:

SELECT COUNT(*), COUNT(email), COUNT(dept_id), COUNT(city), COUNT(manager_id)
FROM employees;
+----------+--------------+----------------+-------------+-------------------+
| COUNT(*) | COUNT(email) | COUNT(dept_id) | COUNT(city) | COUNT(manager_id) |
+----------+--------------+----------------+-------------+-------------------+
|       10 |            9 |              9 |           9 |                 9 |
+----------+--------------+----------------+-------------+-------------------+
1 row in set (0.00 sec)

Each of these columns has exactly one NULL (Meera's email, Ishita's department and city, Aarav's manager).

COUNT(*) vs COUNT(1)

You will often see COUNT(1) in other people's code. The 1 is a constant that is never NULL, so it counts every row, exactly like COUNT(*). COUNT(NULL), on the other hand, is always 0:

SELECT COUNT(1), COUNT(NULL) FROM employees;
+----------+-------------+
| COUNT(1) | COUNT(NULL) |
+----------+-------------+
|       10 |           0 |
+----------+-------------+
1 row in set (0.00 sec)

The myth that COUNT(1) is faster than COUNT(*) is false in MySQL: the documentation states InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) the same way. * here does not mean "read all columns"; it just means "count rows". Prefer COUNT(*) because it says what you mean.

One more fact: InnoDB does not keep a stored row count, because different transactions may see different numbers of rows at the same moment. So COUNT(*) on a huge table has to scan an index and can be slow.


SUM: Totals

Total value of all orders:

SELECT SUM(amount) AS total_sales FROM orders;
+-------------+
| total_sales |
+-------------+
|    26250.00 |
+-------------+
1 row in set (0.00 sec)

That includes the cancelled and pending orders. Combine with WHERE to total only delivered ones. WHERE runs first, then the aggregate is computed over the rows that are left:

SELECT SUM(amount) AS delivered_sales FROM orders WHERE status = 'Delivered';
+-----------------+
| delivered_sales |
+-----------------+
|        17700.00 |
+-----------------+
1 row in set (0.00 sec)

Check: 2500 + 4800 + 7300 + 3100 = 17700 (orders 1001, 1002, 1004, 1006).

The argument can be any expression, evaluated row by row before summing. The company's annual salary bill:

SELECT SUM(salary * 12) AS annual_payroll FROM employees;
+----------------+
| annual_payroll |
+----------------+
|     9900000.00 |
+----------------+
1 row in set (0.00 sec)

Counting with SUM (a MySQL Trick)

In MySQL a comparison returns 1 (true) or 0 (false). Summing a comparison therefore counts how many rows satisfy it, which lets you count several categories in one pass:

SELECT SUM(status = 'Delivered') AS delivered,
       SUM(status = 'Pending')   AS pending,
       SUM(status = 'Cancelled') AS cancelled,
       COUNT(*)                  AS total
FROM orders;
+-----------+---------+-----------+-------+
| delivered | pending | cancelled | total |
+-----------+---------+-----------+-------+
|         4 |       2 |         1 |     8 |
+-----------+---------+-----------+-------+
1 row in set (0.00 sec)

(The remaining order is Shipped.) This shortcut is MySQL-specific. The portable way uses SUM(CASE WHEN ... THEN 1 ELSE 0 END), which you will learn in the CASE Expression chapter.

Caution: MySQL is lenient about types. SUM(name) does not raise an error; it converts each name to the number 0, returns 0 and produces ten warnings. Always check that you are summing a numeric column.


AVG: Averages

SELECT AVG(salary) AS avg_salary FROM employees;
+--------------+
| avg_salary   |
+--------------+
| 82500.000000 |
+--------------+
1 row in set (0.00 sec)

825000 / 10 = 82500. MySQL returns extra decimal places for AVG (for a DECIMAL column it adds 4 digits to the scale). Use ROUND to tidy the output:

SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;
+------------+
| avg_salary |
+------------+
|   82500.00 |
+------------+
1 row in set (0.00 sec)

Several aggregates can appear in the same SELECT. A full salary profile of Engineering:

SELECT COUNT(*)    AS n,
       SUM(salary) AS total,
       AVG(salary) AS average,
       MIN(salary) AS low,
       MAX(salary) AS high
FROM employees
WHERE dept_id = 10;
+---+-----------+---------------+----------+-----------+
| n | total     | average       | low      | high      |
+---+-----------+---------------+----------+-----------+
| 3 | 340000.00 | 113333.333333 | 95000.00 | 150000.00 |
+---+-----------+---------------+----------+-----------+
1 row in set (0.00 sec)

Note that AVG is exactly SUM / COUNT of the same values: 340000 / 3 = 113333.33.


MIN and MAX: Extremes

SELECT MIN(salary)               AS lowest,
       MAX(salary)               AS highest,
       MAX(salary) - MIN(salary) AS salary_range
FROM employees;
+----------+-----------+--------------+
| lowest   | highest   | salary_range |
+----------+-----------+--------------+
| 48000.00 | 150000.00 |    102000.00 |
+----------+-----------+--------------+
1 row in set (0.00 sec)

As the example shows, you can do arithmetic with aggregate results. Unlike SUM and AVG, MIN and MAX also work on dates and strings:

SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire FROM employees;
+------------+-------------+
| first_hire | latest_hire |
+------------+-------------+
| 2017-11-20 | 2024-01-10  |
+------------+-------------+
1 row in set (0.00 sec)

For dates, "smaller" means earlier. For strings, it follows alphabetical order (by the collation):

SELECT MIN(name), MAX(name) FROM employees;
+--------------+--------------+
| MIN(name)    | MAX(name)    |
+--------------+--------------+
| Aarav Sharma | Vikram Singh |
+--------------+--------------+
1 row in set (0.00 sec)
SELECT MIN(city), MAX(city) FROM employees;
+-----------+-----------+
| MIN(city) | MAX(city) |
+-----------+-----------+
| Bengaluru | Mumbai    |
+-----------+-----------+
1 row in set (0.00 sec)

Interesting: in ORDER BY & LIMIT we saw that NULL sorts first in ascending order, yet MIN(city) is Bengaluru, not NULL. That is because aggregate functions ignore NULL completely, as we will now see.


Aggregates and NULL

Rule: the standard aggregate functions (COUNT, SUM, AVG, MIN, MAX) ignore NULL values. The only exception is COUNT(*), which counts rows rather than values.

This sounds harmless but has real consequences for AVG. Imagine four students' exam scores, where one student was absent (NULL). We can build this tiny table on the fly with VALUES:

SELECT AVG(score)              AS avg_ignoring_null,
       SUM(score) / COUNT(*)   AS avg_over_all_rows,
       AVG(COALESCE(score, 0)) AS avg_null_as_zero,
       COUNT(score),
       COUNT(*)
FROM (VALUES ROW(80), ROW(60), ROW(NULL), ROW(100)) AS t(score);
+-------------------+-------------------+------------------+--------------+----------+
| avg_ignoring_null | avg_over_all_rows | avg_null_as_zero | COUNT(score) | COUNT(*) |
+-------------------+-------------------+------------------+--------------+----------+
|           80.0000 |           60.0000 |          60.0000 |            3 |        4 |
+-------------------+-------------------+------------------+--------------+----------+
1 row in set (0.00 sec)

Step by step:

Values:            80, 60, NULL, 100
AVG(score):        (80 + 60 + 100) / 3 = 240 / 3 = 80     (NULL row not counted)
SUM / COUNT(*):    240 / 4 = 60                           (absent student counted as 0)
AVG(COALESCE(..)): (80 + 60 + 0 + 100) / 4 = 60

Which is "correct"? It depends on the meaning. If the absent student should not affect the class average, 80 is right. If an absence counts as zero marks, 60 is right. The important thing is to decide consciously; SQL will choose "ignore NULL" for you unless you use COALESCE (from the NULL Values chapter).

Function

NULL values

COUNT(*)

Counted (it counts rows)

COUNT(col), COUNT(DISTINCT col)

Skipped

SUM, AVG

Skipped (not treated as 0)

MIN, MAX

Skipped


Aggregates on an Empty Set

What if WHERE matches no rows at all? There are no "Returned" orders:

SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders
WHERE status = 'Returned';
+----------+-------------+-------------+-------------+-------------+
| COUNT(*) | SUM(amount) | AVG(amount) | MIN(amount) | MAX(amount) |
+----------+-------------+-------------+-------------+-------------+
|        0 |        NULL |        NULL |        NULL |        NULL |
+----------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

Two things to notice:

  • We still get one row. An aggregate query without GROUP BY always returns exactly one row, even over zero input rows.

  • COUNT returns 0, but SUM, AVG, MIN and MAX return NULL, not 0. There is no sum of nothing; the answer is "unknown".

This regularly causes bugs: a report shows a blank instead of 0, or application code crashes on NULL. If you want 0, say so:

SELECT IFNULL(SUM(amount), 0) AS returned_total
FROM orders
WHERE status = 'Returned';
+----------------+
| returned_total |
+----------------+
|           0.00 |
+----------------+
1 row in set (0.00 sec)

DISTINCT Inside Aggregates

You saw COUNT(DISTINCT ...) in the DISTINCT chapter. How many departments and cities do our employees cover?

SELECT COUNT(DISTINCT dept_id) AS departments_in_use,
       COUNT(DISTINCT city)    AS cities
FROM employees;
+--------------------+--------+
| departments_in_use | cities |
+--------------------+--------+
|                  4 |      7 |
+--------------------+--------+
1 row in set (0.00 sec)

DISTINCT also works with SUM and AVG, but think hard before using it:

SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
+----------------------+-------------+
| SUM(DISTINCT salary) | SUM(salary) |
+----------------------+-------------+
|            730000.00 |   825000.00 |
+----------------------+-------------+
1 row in set (0.00 sec)

The difference is 95000: Priya and Rohan earn the same amount, so SUM(DISTINCT salary) counted it only once. As a payroll figure, that is simply wrong. Two different people with the same salary are still two salaries. SUM(DISTINCT ...) is rarely what a business question needs.


Two Errors Every Beginner Meets

Error 1: Mixing Aggregates with Plain Columns

"Show me the highest salary and who earns it." A natural first attempt:

SELECT name, MAX(salary) FROM employees;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'company_db.employees.name'; this is incompatible with sql_mode=only_full_group_by

Think about the shape of the result. MAX(salary) collapses 10 rows into 1 value. name still has 10 values. Which of the 10 names should go next to the single maximum? SQL has no rule that says "the name from the row where the max came from", so the query is ambiguous, and MySQL's default ONLY_FULL_GROUP_BY mode rejects it.

In an aggregate query, every column in SELECT must be either inside an aggregate function or listed in GROUP BY.

(Very old MySQL versions, before 5.7, did not have this check turned on and returned the name from an arbitrary row, which led to many silent bugs. If you see a tutorial where this query "works", that is why.)

Error 2: Aggregates in WHERE

Second attempt:

SELECT name, salary FROM employees WHERE salary = MAX(salary);
ERROR 1111 (HY000): Invalid use of group function

Remember the processing order: WHERE filters individual rows before any aggregation happens. While WHERE looks at Aarav's row, the maximum has not been computed yet, so it cannot be used. (Conditions on aggregate results go in HAVING, covered in the next chapter.)

The Fix: Compute the Aggregate First

Put the aggregate in its own small query (a subquery), so it is calculated first and then used as an ordinary value:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
+--------------+-----------+
1 row in set (0.00 sec)

This version is also correct when several people share the top salary: all of them are returned. Same pattern, "who earns more than the average?":

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Sneha Kapoor | 110000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)

Subqueries get two full chapters later on; for now, just remember the pattern.


Bonus: Other Aggregate Functions in MySQL

Beyond the big five, MySQL offers more. Two worth knowing:

GROUP_CONCAT joins strings from many rows into one:

SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS hr_team
FROM employees
WHERE dept_id = 30;
+-------------------------+
| hr_team                 |
+-------------------------+
| Karan Patel, Meera Nair |
+-------------------------+
1 row in set (0.00 sec)

(By default the result is cut at 1024 characters, controlled by the group_concat_max_len setting. PostgreSQL's equivalent is STRING_AGG.)

Statistics: STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP for spread:

SELECT STDDEV_POP(salary), VAR_POP(salary) FROM employees;
+--------------------+-----------------+
| STDDEV_POP(salary) | VAR_POP(salary) |
+--------------------+-----------------+
|  29577.86334406189 |       874850000 |
+--------------------+-----------------+
1 row in set (0.00 sec)

Others include BIT_AND, BIT_OR, JSON_ARRAYAGG and JSON_OBJECTAGG. Most aggregates can also be used as window functions (with OVER), which compute a summary while keeping every row, for example each employee's salary next to the department average. That is covered in Window Functions.


Common Mistakes

Mistake

What happens

Fix

Using COUNT(col) to count rows

Rows with NULL in col are not counted

Use COUNT(*) for rows

Assuming AVG treats NULL as 0

Average is computed only over non-NULL values

AVG(COALESCE(col, 0)) if NULL should count as 0

Expecting SUM of no rows to be 0

Returns NULL

IFNULL(SUM(col), 0) or COALESCE

SELECT name, MAX(salary) FROM t

Error 1140 (only_full_group_by)

Subquery: WHERE salary = (SELECT MAX(salary) ...)

Aggregate inside WHERE

Error 1111

Subquery, or HAVING for groups

SUM(DISTINCT col) for totals

Equal values counted once; total too low

Use plain SUM(col)

Summing a text column

MySQL returns 0 with warnings, not an error

Check column types; run SHOW WARNINGS

Showing raw AVG output to users

Long decimals like 113333.333333

ROUND(AVG(col), 2)


Interview Questions

What are aggregate functions? Name five.

Functions that compute one value from a set of rows: COUNT, SUM, AVG, MIN, MAX.

Difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts all rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts the number of different non-NULL values.

Is COUNT(1) faster than COUNT(*)?

No. In MySQL InnoDB they are processed the same way and both count all rows. COUNT(*) is the clearer choice.

How do aggregate functions handle NULL?

They ignore NULL values. Only COUNT(*) includes rows with NULLs, because it counts rows rather than values.

What do SUM and COUNT return for an empty table?

COUNT returns 0; SUM, AVG, MIN, MAX return NULL. Wrap with COALESCE(SUM(x), 0) if you need 0.

Values are 10, 20, NULL, 30. What are AVG and SUM/COUNT(*)?

AVG = 60 / 3 = 20 (NULL ignored). SUM / COUNT(*) = 60 / 4 = 15.

Why can't you use an aggregate function in WHERE?

WHERE is evaluated row by row before aggregation, so aggregate values do not exist yet. Use a subquery, or HAVING to filter groups after aggregation.

How do you find the employee(s) with the highest salary?

SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); This also returns everyone tied at the top.

Can MIN and MAX be used on strings and dates?

Yes. Dates compare chronologically, strings alphabetically according to the collation. SUM and AVG are meant for numbers.

How many rows does SELECT COUNT(*) FROM t WHERE 1 = 0 return?

One row, containing 0. An aggregate query without GROUP BY always returns exactly one row.


Summary

  • Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN, MAX.

  • WHERE filters rows first; the aggregate is computed over what is left.

  • COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts different non-NULL values.

  • COUNT, SUM, AVG, MIN and MAX ignore NULL (except COUNT(*)), which directly affects AVG.

  • On zero rows: COUNT is 0, the others are NULL; use IFNULL/COALESCE for 0.

  • MIN/MAX also work on dates and strings.

  • You cannot mix plain columns with aggregates without GROUP BY (error 1140), or use aggregates in WHERE (error 1111); use a subquery instead.

So far we have one summary for the whole table. But managers usually ask "per department", "per customer", "per month". In the next chapter, GROUP BY & HAVING, we will split rows into groups, aggregate each group, and filter groups by their totals.