MySQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

An aggregate function looks at many rows and returns one value: a count, a total, an average, the largest value.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT COUNT(*)      AS products,
       SUM(stock)    AS total_units,
       AVG(price)    AS average_price,
       MIN(price)    AS cheapest,
       MAX(price)    AS most_expensive
FROM products;

Output

OUTPUT

+----------+-------------+---------------+----------+----------------+
| products | total_units | average_price | cheapest | most_expensive |
+----------+-------------+---------------+----------+----------------+
|        8 |         788 |    324.312500 |    15.50 |         999.00 |
+----------+-------------+---------------+----------+----------------+
1 row in set

Eight rows went in, and one row came out. That is the core idea.

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
`GROUP_CONCAT(column)`	Values joined into one text

With a WHERE clause

WHERE filters the rows before they are summarised:

query.sql

SQL

1
2
3

SELECT COUNT(*) AS stationery_products, ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE category = 'stationery';

Output

OUTPUT

+---------------------+-----------+
| stationery_products | avg_price |
+---------------------+-----------+
|                   4 |     68.88 |
+---------------------+-----------+
1 row in set

Aggregates ignore NULL

All of them except COUNT(*) skip NULL values:

query.sql

SQL

CREATE TABLE scores (player VARCHAR(10), points INT);
INSERT INTO scores VALUES ('Asha', 10), ('Ben', NULL), ('Chitra', 20);

SELECT COUNT(*) AS rows_counted, COUNT(points) AS points_counted, SUM(points) AS total, AVG(points) AS average FROM scores;

Output

OUTPUT

+--------------+----------------+-------+---------+
| rows_counted | points_counted | total | average |
+--------------+----------------+-------+---------+
|            3 |              2 |    30 | 15.0000 |
+--------------+----------------+-------+---------+
1 row in set

The average is 15 (30 divided by 2), not 10 (30 divided by 3). Ben’s NULL was left out of both the sum and the count. Whether that is correct depends on what NULL means. If “no score” should count as 0, write AVG(COALESCE(points, 0)).

Aggregates and normal columns do not mix

You cannot select an ordinary column next to an aggregate unless you group by it:

query.sql

SQL

SELECT name, MAX(price) FROM products;

Output

OUTPUT

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

With ONLY_FULL_GROUP_BY (the default), MySQL refuses: which name would go with the maximum? The question needs GROUP BY or a different approach (GROUP BY, Top-N queries).

Aggregates in the order of evaluation

Aggregates are calculated after WHERE and GROUP BY. That is why you cannot use them in WHERE, and why HAVING exists (Order of Evaluation).

Aggregates on an empty set

If no rows match, COUNT returns 0, and the others return NULL:

query.sql

SQL

SELECT COUNT(*) AS rows_found, SUM(price) AS total, MAX(price) AS biggest FROM products WHERE category = 'toys';

Output

OUTPUT

+------------+-------+---------+
| rows_found | total | biggest |
+------------+-------+---------+
|          0 |  NULL |    NULL |
+------------+-------+---------+
1 row in set

Wrap with COALESCE(SUM(price), 0) if you need a zero.

Window functions are the other kind

The same functions can also be used as window functions (SUM(x) OVER (...)), which keep every row and add the summary beside it (Window Functions).

lightbulb

Check what NULL means

Before you trust an AVG or a SUM, ask whether the missing values are “unknown” (skip them) or “zero” (count them).