Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 20 of 50

COUNT, SUM, AVG, MIN and MAX

Summarise many rows into one value with COUNT, SUM, AVG, MIN, MAX and GROUP_CONCAT, and learn how aggregates treat NULL and empty tables, with verified output.

13 minSummarising dataUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCount, total, average and find extremes across rows, and predict how aggregates handle NULL and empty results.

From rows to a single answer

Most business questions are about totals, not individual rows: how many customers, how much revenue, what is the most expensive product. Aggregate functions calculate one value from a whole set of rows.

Function Result
COUNT(*) number of rows
COUNT(column) number of rows where the column is not NULL
SUM(column) total
AVG(column) average
MIN(column) / MAX(column) smallest / largest value

COUNT

sql
SELECT COUNT(*) AS customers, COUNT(phone) AS with_phone, COUNT(DISTINCT country) AS countries FROM customers;
result
+-----------+------------+-----------+
| customers | with_phone | countries |
+-----------+------------+-----------+
|        12 |          8 |        10 |
+-----------+------------+-----------+

COUNT(*) counts every row. COUNT(phone) skips the four customers with a NULL phone. COUNT(DISTINCT country) counts each country once.

You can combine COUNT with WHERE to count a subset:

sql
SELECT COUNT(*) AS delivered_orders FROM orders WHERE status = 'delivered';
result
+------------------+
| delivered_orders |
+------------------+
|               11 |
+------------------+

SUM and AVG

sql
SELECT SUM(stock) AS units_in_stock, AVG(price) AS average_price, ROUND(AVG(price), 2) AS average_rounded FROM products;
result
+----------------+---------------+-----------------+
| units_in_stock | average_price | average_rounded |
+----------------+---------------+-----------------+
|           1670 |     46.210000 |           46.21 |
+----------------+---------------+-----------------+

SUM adds the values and AVG divides the sum by the number of non-NULL values. Because AVG of a DECIMAL has extra precision, wrap it in ROUND for display.

The calculation can be an expression. Revenue is quantity times unit price on every order line:

sql
SELECT SUM(quantity * unit_price) AS total_revenue, SUM(quantity) AS units_sold FROM order_items;
result
+---------------+------------+
| total_revenue | units_sold |
+---------------+------------+
|       2049.81 |         51 |
+---------------+------------+

That includes lines from cancelled orders. Excluding them needs a join, which you will write shortly.

MIN and MAX

sql
SELECT MIN(price) AS cheapest, MAX(price) AS dearest FROM products;
result
+----------+---------+
| cheapest | dearest |
+----------+---------+
|    12.00 |  199.99 |
+----------+---------+
sql
SELECT MIN(signup_date) AS first_signup, MAX(signup_date) AS latest_signup FROM customers;
result
+--------------+---------------+
| first_signup | latest_signup |
+--------------+---------------+
| 2023-01-15   | 2023-09-12    |
+--------------+---------------+

MIN and MAX work on numbers, dates and text (alphabetically).

Aggregates and NULL

Aggregate functions ignore NULL values, except COUNT(*). On the sample data, only eight customers have a phone, so:

sql
SELECT COUNT(*) AS all_rows, COUNT(phone) AS non_null_phones, MIN(phone) AS smallest_phone FROM customers;
result
+----------+-----------------+-----------------+
| all_rows | non_null_phones | smallest_phone  |
+----------+-----------------+-----------------+
|       12 |               8 | +1-212-555-0010 |
+----------+-----------------+-----------------+

An empty set behaves in a special way. There are no rows to add up, so SUM and AVG are NULL, but a count of nothing is 0:

sql
SELECT COUNT(*) AS n, SUM(price) AS total, AVG(price) AS average, MAX(price) AS biggest FROM products WHERE price > 10000;
result
+---+-------+---------+---------+
| n | total | average | biggest |
+---+-------+---------+---------+
| 0 |  NULL |    NULL |    NULL |
+---+-------+---------+---------+

Use COALESCE(SUM(x), 0) when you want zero instead of NULL on an empty set.

GROUP_CONCAT

GROUP_CONCAT is a MySQL aggregate that joins values from many rows into one string:

sql
SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS electronics FROM products WHERE category_id = 1;
result
+-----------------------------------------------------------------------------+
| electronics                                                                 |
+-----------------------------------------------------------------------------+
| Mechanical Keyboard, Noise-Cancelling Headphones, USB-C Hub, Wireless Mouse |
+-----------------------------------------------------------------------------+

It is useful for reports and comma-separated lists. The result is cut off at group_concat_max_len (1024 characters by default).

One row only

An aggregate query without GROUP BY always returns exactly one row. That is why you cannot mix an aggregate with a plain column:

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

MySQL cannot know which name goes with the maximum price, so it refuses (this strictness is controlled by sql_mode=ONLY_FULL_GROUP_BY, on by default). To get the name of the dearest product, sort and limit, or use a subquery:

sql
SELECT name, price FROM products ORDER BY price DESC LIMIT 1;
result
+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+

The next lesson shows how GROUP BY produces one row per group instead of one row in total.

Common mistakes

  • Using COUNT(column) when you need COUNT(*), skipping NULLs.
  • Mixing aggregates and plain columns without GROUP BY.
  • Using an aggregate in WHERE. WHERE price > AVG(price) is invalid; the HAVING lesson and subqueries solve it.
  • Forgetting that AVG ignores NULL, which may not match your meaning of "average per customer".
  • Not handling an empty set, so reports show NULL instead of 0.

Exercise

Write one query that returns, for the employees table, the number of employees, the total salary bill, the average salary rounded to the nearest whole number, and the earliest and latest hire dates.

Show one possible answer
sql
SELECT COUNT(*) AS staff,
       SUM(salary) AS salary_bill,
       ROUND(AVG(salary)) AS average_salary,
       MIN(hire_date) AS first_hire,
       MAX(hire_date) AS last_hire
FROM employees;
result
+-------+-------------+----------------+------------+------------+
| staff | salary_bill | average_salary | first_hire | last_hire  |
+-------+-------------+----------------+------------+------------+
|    11 |   984000.00 |          89455 | 2018-03-01 | 2024-01-08 |
+-------+-------------+----------------+------------+------------+

Key points

  • Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN and MAX.
  • COUNT(*) counts rows, COUNT(column) counts non-NULL values and COUNT(DISTINCT column) counts different values.
  • SUM, AVG, MIN and MAX ignore NULLs. On an empty set COUNT returns 0 while the others return NULL.
  • Without GROUP BY, an aggregate query returns exactly one row. You cannot mix plain columns and aggregates there.

Check yourself

1. A table has 10 rows and a column with 3 NULLs. What does AVG(column) average?
2. What does SELECT COUNT(*) FROM products WHERE price > 10000; return when nothing matches?
3. Which counts the number of different countries?
4. What is wrong with SELECT name, MAX(price) FROM products;?