Aggregate Functions – Calculating Results

So far, every query we've written returns multiple rows – one row per matching record. Aggregate functions collapse multiple rows into a single calculated result: counts, sums, averages, minimums, and maximums. These functions are essential for validation queries that verify totals, check quantities, or calculate metrics.

COUNT – Counting Rows

The COUNT function returns the number of rows that match your query:

-- How many total users are in the database?
SELECT COUNT(*) FROM Users;

This returns a single number – the total row count in the Users table. COUNT(*) counts all rows, including those with NULL values in some columns.

You can count specific columns instead:

-- How many users have a non-NULL phone number?
SELECT COUNT(PhoneNumber) FROM Users;

COUNT(column) counts only rows where that column is NOT NULL. This distinction matters:

-- Count all orders
SELECT COUNT(*) FROM Orders;  -- Returns 1000

-- Count orders that have shipped (ShipmentDate is not NULL)
SELECT COUNT(ShipmentDate) FROM Orders;  -- Returns 750

The difference (250) is orders that haven't shipped yet (ShipmentDate is NULL).

Combine COUNT with WHERE for filtered counts:

-- How many active users?
SELECT COUNT(*) AS ActiveUserCount
FROM Users
WHERE IsActive = 1;

-- How many orders are in "Processing" status?
SELECT COUNT(*) AS ProcessingOrders
FROM Orders
WHERE Status = 'Processing';

Notice the use of column aliases (AS ActiveUserCount) – without an alias, the result column would be labeled "COUNT(*)" which is less readable.

SUM – Totaling Numeric Values

The SUM function adds up all values in a numeric column:

-- What's the total revenue from all orders?
SELECT SUM(TotalAmount) AS TotalRevenue
FROM Orders;

-- What's the total revenue from completed orders only?
SELECT SUM(TotalAmount) AS CompletedRevenue
FROM Orders
WHERE Status = 'Completed';

SUM ignores NULL values – if a row has NULL in the summed column, it doesn't contribute to the total (it's not treated as zero).

Testing Context: SUM is perfect for validation queries that check financial calculations:

-- Verify total inventory value
SELECT SUM(Price * StockQuantity) AS TotalInventoryValue
FROM Products
WHERE IsActive = 1;

Your test can compare this calculated value against an expected inventory value to catch discrepancies.

AVG – Calculating Averages

The AVG function calculates the average (mean) of numeric values:

-- What's the average order value?
SELECT AVG(TotalAmount) AS AverageOrderValue
FROM Orders;

-- What's the average product price per category?
SELECT AVG(Price) AS AveragePrice
FROM Products
WHERE CategoryId = 3;

Like SUM, AVG ignores NULL values. If you have 10 orders with TotalAmount values and 2 orders with NULL TotalAmount, AVG calculates the mean of the 10 non-NULL values (not 12).

Testing Context: AVG helps validate performance metrics:

-- What's the average API response time today?
SELECT AVG(ResponseTimeMs) AS AvgResponseTime
FROM ApiLogs
WHERE LogDate = CURRENT_DATE;

Your test can assert that average response time stays below a threshold (e.g., under 200ms).

MIN and MAX – Finding Extremes

The MIN and MAX functions return the smallest and largest values in a column:

-- What's the cheapest product?
SELECT MIN(Price) AS CheapestPrice
FROM Products;

-- What's the most expensive product?
SELECT MAX(Price) AS MostExpensivePrice
FROM Products;

-- When was the first order placed?
SELECT MIN(OrderDate) AS FirstOrderDate
FROM Orders;

-- When was the most recent order?
SELECT MAX(OrderDate) AS MostRecentOrder
FROM Orders;

MIN and MAX work with numbers, dates, and even strings (alphabetical order). They ignore NULL values.

Testing Context: MIN and MAX help validate boundary conditions:

-- Verify no products have negative prices (data integrity check)
SELECT MIN(Price) AS MinPrice
FROM Products;
-- Assert MinPrice >= 0 in your test

-- Find the oldest unprocessed order (could indicate a stuck workflow)
SELECT MIN(OrderDate) AS OldestPendingOrder
FROM Orders
WHERE Status = 'Pending';

Combining Multiple Aggregates

You can use multiple aggregate functions in a single query:

-- Get comprehensive order statistics
SELECT COUNT(*) AS TotalOrders,
       SUM(TotalAmount) AS TotalRevenue,
       AVG(TotalAmount) AS AverageOrderValue,
       MIN(TotalAmount) AS SmallestOrder,
       MAX(TotalAmount) AS LargestOrder
FROM Orders
WHERE Status = 'Completed';

This single query returns one row with five calculated values – a complete statistical summary of completed orders.

Aggregates with WHERE Filtering

WHERE clauses filter rows before aggregation occurs:

-- Revenue from orders placed in 2024
SELECT SUM(TotalAmount) AS Revenue2024
FROM Orders
WHERE OrderDate >= '2024-01-01'
  AND OrderDate < '2025-01-01';

-- Average price of active products in category 5
SELECT AVG(Price) AS AvgPrice
FROM Products
WHERE CategoryId = 5
  AND IsActive = 1;

The database first filters rows (applying WHERE), then calculates the aggregate function on the filtered results.

Aggregate functions transform SQL from a row-retrieval tool into a calculation engine. They're essential for validation queries that need to verify counts, totals, averages, or ranges. Next, we'll learn GROUP BY, which lets you calculate aggregates for each category in your data rather than across all rows.