SQL Aggregate Functions

Every query so far has returned one row of output per row of input, filtered or sorted, but still one-to-one. Beacon Robotics' finance team now has a different kind of question: not "show me every employee's salary," but "what's the total payroll?" or "how many employees do we have?" Those questions don't want one answer per row, they want one answer for the whole table. That's what an aggregate function is for.

Learning Objectives

• Define what an aggregate function is and how it differs from a regular column.
• Name the five core aggregate functions and what each one computes.
• Understand how COUNT(*), COUNT(column), SUM, AVG, MIN, and MAX each handle NULL values.

What Is an Aggregate Function?

An aggregate function takes a whole column of values, across many rows, and collapses them into a single summary value, a total, a count, an average, a smallest, or a largest. This is fundamentally different from every function or operator covered so far. salary * 1.10 produces one output per row, one new value for each employee. SUM(salary) produces exactly one output, period, no matter how many rows it looked at.

The Five Core Aggregate Functions

All five are standard SQL, spelled and behaving identically on MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle. This module gives each one its own full lesson; this page covers what they have in common first.

Aggregate Functions vs. Regular Columns

Notice neither query lists first_name or department, an aggregate function is already summarizing every row into one, there's nothing row-specific left to show alongside it. Mixing a raw column with an aggregate function in the same SELECT, without GROUP BY, is one of the most common mistakes beginners make here, covered below and fixed properly once GROUP BY arrives in Module 5.

SELECT COUNT(*) AS employee_count
FROM employees;

SELECT SUM(salary) AS total_payroll
FROM employees;

COUNT(*) vs. COUNT(column): How NULL Is Handled

This same NULL-skipping behavior applies to SUM(), AVG(), MIN(), and MAX() too, every one of them ignores NULL values in the column entirely rather than treating NULL as zero. AVG(salary) on a table where one employee's salary is NULL divides by the number of employees that actually have a salary value, not the total row count.

Try It Yourself

Write a query, portable across all six databases in this course, that returns both the total number of employees and the total payroll, in one query.

SELECT COUNT(*) AS employee_count, SUM(salary) AS total_payroll
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Don't mix raw, non-aggregated columns with aggregate functions in the same SELECT unless GROUP BY is also present.
• Use COUNT(*) to count rows, and COUNT(column) specifically when the goal is counting non-NULL values in that column.
• Remember NULL is excluded, not zeroed, by SUM, AVG, MIN, and MAX, when a column might contain NULLs.

Interview Questions

Summary

An aggregate function collapses many rows into one summary value, unlike a regular column or arithmetic expression, which produces one output per row. The five core functions, COUNT, SUM, AVG, MIN, and MAX, are standard across every database in this course, and all of them (except COUNT(*)) ignore NULL values rather than treating them as zero.

What's Next?

The next lesson covers MIN() and MAX() in depth, finding the smallest and largest values in a column.