When working with relational databases, retrieving raw rows of data is only half the battle. Often, you need to summarize, count, or calculate averages from your database records to build dashboards and reports. To do this, developers rely on aggregate functions in sql to perform calculations on groups of values.

In this guide, we will examine the five core aggregate functions in sql, detail how they handle null values, and show how to pair them with grouping clauses to write powerful analytics queries.

What is an Aggregate Function in SQL?

An aggregate function performs a calculation on a set of values (a column of data) and returns a single summary value. These functions are widely used in daily data operations and form the foundation of database reporting.

The 5 Standard SQL Aggregate Functions

There are five standard aggregate functions supported by virtually all Relational Database Management Systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle:

1. COUNT() – Counting Records

The COUNT() function returns the number of rows that match a specific criteria. You can use it in two ways:

• COUNT(*): Counts every row in the table, including duplicate rows and rows containing NULL values.
• COUNT(column_name): Counts only the rows where the specified column is not NULL.

// Count total employees
SELECT COUNT(*) AS total_employees FROM employees;

// Count employees who have a registered commission percentage (ignores NULLs)
SELECT COUNT(commission_pct) AS employees_with_commission FROM employees;

2. SUM() – Calculating Totals

The SUM() function returns the total sum of a numeric column. It completely ignores NULL values during calculation:

// Calculate total monthly payroll expenditure
SELECT SUM(salary) AS total_payroll FROM employees;

3. AVG() – Calculating Averages

The AVG() function returns the average value of a numeric column. Like SUM(), it ignores NULL values. If you want to compute the average of unique values only, you can include the DISTINCT keyword inside the function:

// Get average employee salary
SELECT AVG(salary) AS average_salary FROM employees;

// Get average salary counting unique salary figures only
SELECT AVG(DISTINCT salary) AS average_unique_salary FROM employees;

4. MIN() and MAX() – Finding Range Extremes

The MIN() and MAX() functions return the smallest and largest values in a column, respectively. Unlike numeric-only functions like SUM() and AVG(), you can use MIN() and MAX() on numbers, text strings (alphabetical order), and date columns:

// Find the lowest salary, highest salary, and latest hire date
SELECT MIN(salary) AS lowest_salary, 
       MAX(salary) AS highest_salary, 
       MAX(hire_date) AS newest_employee_hire_date 
FROM employees;

Comparing Aggregate Function Behaviors

Grouping and Filtering (GROUP BY & HAVING)

To perform calculations across departments or categories, you must pair aggregate functions with the GROUP BY clause. If you want to filter the grouped results, you must use HAVING instead of the standard WHERE clause:

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Summary

SQL aggregate functions are critical tools for data analysis, reporting, and dashboard generation. Always remember that aggregate functions ignore NULL values, which can alter your averages if you have empty rows. To see how these calculations are used within nested queries, check out our guide on Subqueries in SQL. You can also explore the official MySQL Aggregate Functions Reference Guide for advanced functions like standard deviation.