Aggregate Functions

So far every query has returned rows from a table. Managers usually want something different: one number that sums up many rows. How many customers do we have? What is our total sales value? What is the average product price? When was our first order?

Aggregate functions answer these questions. They take a whole set of rows and squeeze it into a single value. In this lesson you will use the five main ones, learn the important difference between COUNT(*) and COUNT(column), and see how NULL affects the results.

The five aggregate functions

You use them in the SELECT list. Without GROUP BY, which you will meet in the next lesson, the whole table becomes one group and the result is exactly one row.

COUNT(*) and COUNT(column)

COUNT comes in three forms, and they answer three different questions:

SELECT COUNT(*), COUNT(email), COUNT(DISTINCT city)
FROM customers;

COUNT(*) | COUNT(email) | COUNT(DISTINCT city)
---------+--------------+---------------------
12       | 10           | 11
(1 row)

• COUNT(*) counts rows. There are 12 customers.
• COUNT(email) counts rows where email is not NULL. Ananya and Arjun have no email, so the answer is 10.
• COUNT(DISTINCT city) counts different non-NULL values. Two customers share Rohtak, so there are 11 cities.

This difference matters. If you want "how many customers", use COUNT(*). If you want "how many customers can we email", COUNT(email) gives it to you directly. Choosing the wrong one gives a number that looks reasonable but is wrong.

SUM and AVG

SUM adds up the values in a column, and the column can be a calculation. Here are the total units sold and the total value of all order lines:

SELECT SUM(quantity) AS units_sold,
       SUM(quantity * unit_price) AS total_value
FROM order_items;

units_sold | total_value
-----------+------------
32         | 110973
(1 row)

SUM(quantity * unit_price) multiplies each line first and then adds up the results, which is exactly what an invoice total does. Notice that SUM(unit_price) alone would be wrong, because it ignores how many units were bought.

AVG gives the average. Wrap it in ROUND(value, 2) to keep two decimal places:

SELECT ROUND(AVG(price), 2) AS average_price,
       MIN(price) AS cheapest,
       MAX(price) AS dearest
FROM products;

average_price | cheapest | dearest
--------------+----------+--------
4107.42       | 450      | 15999
(1 row)

Without ROUND, the browser shows 4107.416666666667 and MySQL shows 4107.416667; ROUND gives the same clean answer in both. MySQL also prints the prices as 450.00 and 15999.00, as explained in The Practice Database.

MIN and MAX on dates and text

MIN and MAX work on anything that can be put in order. On dates they give the earliest and latest; on text they give the first and last in alphabetical order.

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order
FROM orders;

first_order | latest_order
------------+-------------
2025-01-05  | 2025-05-20
(1 row)

Aggregates after WHERE

WHERE filters the rows first, and the aggregate function then works on the rows that are left. This gives you a summary of one part of the table:

SELECT COUNT(*) AS products, SUM(stock) AS units, MIN(price) AS cheapest
FROM products
WHERE category = 'Accessories';

products | units | cheapest
---------+-------+---------
6        | 312   | 799
(1 row)

The store has 6 accessories with 312 units in stock, and the cheapest costs ₹799.

NULL and empty results

Aggregate functions skip NULL values. SUM, AVG, MIN and MAX simply ignore them, just as COUNT(column) does. This is usually what you want, but be careful with AVG: an average that skips missing values is not the same as one that treats them as zero. If missing should mean zero, write AVG(COALESCE(column, 0)).

When no rows match at all, COUNT returns 0, but the others return NULL, because there is nothing to add up or compare:

SELECT COUNT(*), SUM(stock)
FROM products
WHERE category = 'Toys';

COUNT(*) | SUM(stock)
---------+-----------
0        | NULL
(1 row)

The store sells no toys. If your app shows this total, use COALESCE(SUM(stock), 0) so the page says 0 instead of showing an empty value.

You cannot mix aggregates and plain columns

A beginner who wants "the most expensive product" often writes this:

SELECT product_name, MAX(price) FROM products;

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

MAX(price) is one value for the whole table, but product_name has twelve different values. MySQL does not know which name to show, so it refuses. (The browser's SQLite runs the query anyway and happens to show the right name, so do not rely on it.) To get the product with the highest price, sort and take the first row: ORDER BY price DESC LIMIT 1. Later, Subqueries show another way.

Common mistakes

• Using COUNT(column) to count rows. It skips NULLs, so it can be smaller than the number of rows. Use COUNT(*) to count rows.
• Summing the wrong thing. SUM(unit_price) is not sales value; SUM(quantity * unit_price) is.
• Putting an aggregate in WHERE. WHERE SUM(quantity) > 2 fails with ERROR 1111 (HY000): Invalid use of group function, because WHERE runs before any totals exist. Conditions on totals go in HAVING.
• Showing NULL as a total. An empty set gives NULL from SUM; wrap it in COALESCE(..., 0).

Key points

• COUNT, SUM, AVG, MIN and MAX turn many rows into one value.
• COUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts different values.
• Aggregates work on the rows left after WHERE.
• NULLs are ignored; on no rows, COUNT gives 0 and the others give NULL.
• Do not mix plain columns with aggregates unless you use GROUP BY.

SELECT COUNT(*) AS order_lines,
       SUM(quantity) AS units_sold,
       SUM(quantity * unit_price) AS total_value,
       MAX(quantity * unit_price) AS biggest_line
FROM order_items;