Aggregate Functions in SQL COUNT, SUM, AVG, MIN and MAX

We have already seen how to group rows with GROUP BY. But those groups are useless if we don’t calculate something with them.

This is where Aggregate Functions come in. An aggregate function takes a collection of values and performs a calculation to return a single value.

They are the fundamental mathematical operations for any report or data analysis. Let’s look at the five “magnificent” ones you’ll use in 99% of your queries.

Counting with COUNT

Its goal is to count elements. It’s the most basic function, but it has two “flavors” that often cause confusion.

-- Imagine an 'Employees' table with 10 rows.
-- 2 employees don't have an email (Email is NULL).

SELECT 
    COUNT(*) AS TotalRows,        -- Returns 10
    COUNT(Email) AS TotalEmails   -- Returns 8
FROM Employees;

Summing with SUM

As its name suggests, it sums all values in a numeric column.

SELECT SUM(Price) AS TotalRevenue
FROM Sales;

Averaging with AVG

Calculates the arithmetic mean (sum divided by count).

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

SELECT AVG(Score) FROM Exams; 
-- Result: 10 (What?!)

-- Correct Way
SELECT AVG(CAST(Score AS DECIMAL(10,2))) 
FROM Exams;
-- Result: 10.50

Minimum and Maximum with MIN and MAX

These functions return the lowest and highest value in a set, respectively.

SELECT 
    MIN(Price) AS CheapestProduct,
    MAX(RegistrationDate) AS LastRegisteredUser,
    MIN(LastName) AS FirstLastNameAlphabetically
FROM Data;

The great thing is that they don’t just work with numbers.

• Numbers: The lowest/highest mathematical value.
• Dates: The oldest date (MIN) or the most future date (MAX).
• Text: The first (MIN) or last (MAX) in alphabetical order.

Aggregations with DISTINCT

Sometimes we don’t want to count everything, but count unique things. All aggregate functions accept the DISTINCT modifier inside their parentheses.

The most common use case is with COUNT.

-- How many sales have we made?
SELECT COUNT(SaleID) FROM Sales; -- e.g., 100 sales

-- How many DIFFERENT customers have bought from us?
SELECT COUNT(DISTINCT CustomerID) FROM Sales; -- e.g., 45 customers

Handling Nulls

It’s vital to remember how each one behaves in the face of that great enemy, NULL.

Combined Example

To finish, let’s look at a typical Dashboard query that uses everything learned:

SELECT 
    Category,
    COUNT(*) AS TotalProducts,
    SUM(Stock) AS TotalInventory,
    MAX(Price) AS HighestPrice,
    AVG(CAST(Price AS DECIMAL(10,2))) AS AveragePrice
FROM Products
GROUP BY Category;