Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are the backbone of reporting and data analysis in SQL.

Definition: The Big 5

There are five primary aggregate functions that are standard across all SQL dialects:

1. COUNT(): Returns the number of rows.
2. SUM(): Returns the total sum of a numeric column.
3. AVG(): Returns the average value of a numeric column.
4. MIN(): Returns the smallest value.
5. MAX(): Returns the largest value.

Context: Aggregates and NULLs

A critical rule in SQL is that aggregate functions ignore NULL values, with one major exception: COUNT(*).

• COUNT(column_name) counts the number of non-null values in that column.
• COUNT(*) counts every row in the result set, including those that are entirely null.

Caution

Because AVG() ignores NULLs, the result might be different than you expect.
If you have values: 10, 20, NULL.
AVG() calculates $(10+20) / 2 = 15$.
If you wanted the average of all three rows (treating NULL as 0), you would need to use AVG(COALESCE(column, 0)).

Detailed Explanation: Syntax

Aggregate functions are used in the SELECT list.

Basic Aggregation

SELECT
  COUNT(*) AS TotalRows,
  SUM(Salary) AS TotalPayroll,
  AVG(Salary) AS AverageSalary,
  MIN(Salary) AS LowestSalary,
  MAX(Salary) AS HighestSalary
FROM Employees;

Example: Sales Performance

We have a Sales table:

SaleID	Amount	Tax
1	100.00	5.00
2	200.00	10.00
3	50.00	NULL
4	NULL	NULL

Task: Calculate basic statistics for the sales amounts.

Aggregates in Action

SELECT
  COUNT(*) AS RawCount,
  COUNT(Amount) AS ValidAmountCount,
  SUM(Amount) AS TotalRevenue,
  MAX(Amount) AS LargestSale
FROM Sales;

Output

RawCount	ValidAmountCount	TotalRevenue	LargestSale
4	3	350.00	200.00

Under the Hood: Stream Aggregation vs. Hash Aggregation

How does the database calculate a sum over 1 billion rows without running out of memory?

1. Stream Aggregation

If the data is already sorted (via an index), the engine can “stream” the data. It keeps a single running total in memory and adds each row to it as it passes by. This uses almost zero memory regardless of the table size.

2. Hash Aggregation

If the data is not sorted and you are grouping (see next chapter), the engine builds a “Hash Table” in memory. Each group becomes a key in the table, and the aggregate value (the running sum) is the value. If the hash table becomes too large for memory, the engine must spill the data to disk, which significantly slows down the query.

Accuracy Note

For SUM and AVG on floating-point numbers (FLOAT/REAL), you might encounter tiny rounding errors (e.g., 0.000000000001) due to how computers handle binary decimals. For financial data, always use DECIMAL or NUMERIC types to ensure exact precision.

Tip

You can combine aggregates with filters:
SELECT SUM(Amount) FROM Sales WHERE Status = 'Completed';