SQL Aggregate Functions

Many questions about data need one number, not a list: how many customers do we have? What is the average product price? When was the latest order? SQL aggregate functions answer these by taking a whole column of values and returning a single result. They are the base for the reports and dashboards you will build with GROUP BY in the next lesson.

The five common SQL aggregate functions

All five work the same way in SQLite, MySQL, PostgreSQL and SQL Server, so what you learn here carries over directly.

COUNT: how many?

COUNT comes in three forms, and the difference matters:

• COUNT(*) counts rows, whatever they contain.
• COUNT(column) counts only rows where that column is not NULL.
• COUNT(DISTINCT column) counts the different non-NULL values.

SELECT COUNT(*)                AS TotalCustomers,
       COUNT(State)            AS WithState,
       COUNT(DISTINCT Country) AS Countries
FROM Customers;

Two customers have no state, so COUNT(State) gives 10, not 12. This makes COUNT(column) a quick way to check how complete your data is.

SUM and AVG

SUM adds up a numeric column and AVG calculates its mean. Combine them with WHERE to summarise just part of the table – the filter runs first, then the function works on the rows that are left.

SELECT SUM(Salary) AS TotalSalary,
       AVG(Salary) AS AverageSalary,
       COUNT(*)    AS People
FROM Employees
WHERE Department = 'Development';

AVG always returns a decimal number in SQLite, even when the inputs are whole numbers. Averages often have long decimal tails, so wrap them in ROUND(value, 2) before showing them to people.

SUM is just as useful for quantities. The OrderDetails table has one row per product per order, so adding up its Quantity column for one product tells you how many packs of it have been ordered in total. Here product 1 is Filter Coffee Powder:

SELECT SUM(Quantity) AS PacksOrdered,
       COUNT(*)      AS OrderLines
FROM OrderDetails
WHERE ProductID = 1;

Note that AVG(x) is the same as SUM(x) / COUNT(x). Both ignore NULL values, so the average is taken over the rows that actually have a value.

MIN and MAX

MIN and MAX find the smallest and largest values. They work on text (alphabetical order) and on our YYYY-MM-DD dates too, which makes them handy for finding the first and last order.

SELECT MIN(Price)           AS Cheapest,
       MAX(Price)           AS Dearest,
       ROUND(AVG(Price), 2) AS AveragePrice
FROM Products;

SELECT MIN(OrderDate) AS FirstOrder,
       MAX(OrderDate) AS LatestOrder
FROM Orders;

How aggregates handle NULL

Apart from COUNT(*), every aggregate function simply skips NULL values. Usually that is what you want, but be careful with averages: if some salaries were missing, AVG(Salary) would average only the known ones, not treat the missing ones as zero.

When no rows match at all, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. To show 0 instead, use COALESCE(SUM(Quantity), 0). COALESCE returns the first value in its list that is not NULL and works in all major databases. SQLite also has its own TOTAL() function, which returns 0.0 instead of NULL, but it does not exist in MySQL.

Where aggregates are used

On a website, aggregate queries fill the numbers in an admin dashboard, the “(24)” next to a category name, or the average star rating on a product page. Because the database does the counting, your PHP or JavaScript code receives one small row instead of thousands. Next, SQL GROUP BY and HAVING shows how to get one result per department, category or customer. To revisit filtering, see SQL WHERE.

Further reading

• sqlite.org/lang_aggfunc.html
• sqlite.org/lang_corefunc.html#coalesce
• dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html
• dev.mysql.com/doc/refman/8.4/en/group-by-handling.html