SQL SUM()

The aggregate functions overview used SUM(salary) to find Beacon Robotics' total payroll as a quick example. This lesson covers SUM() properly, its DISTINCT variant, and a NULL-handling quirk specific to SUM() that trips up a lot of beginners.

Learning Objectives

Use SUM() to total a numeric column.
Use SUM(DISTINCT column) to total only unique values.
Know that SUM() returns NULL, not 0, when no rows match.

A Column

Every salary value

SUM()

Adds every value

One Total

A single number

SUM() on a Numeric Column

SUM() only makes sense on numeric columns, unlike MIN() and MAX(), it can't total up dates or text. Attempting SUM() on a non-numeric column raises an error on every database in this course.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT SUM(salary) AS total_payroll
FROM employees;

SUM() with WHERE

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT SUM(salary) AS engineering_payroll
FROM employees
WHERE department = 'Engineering';

SUM(DISTINCT column)

A narrower use case than COUNT(DISTINCT ...), but valid the same way: if two employees happen to earn the identical salary, that value is only added once. Most real payroll questions want the plain SUM(salary), counting every employee's pay, DISTINCT here is specifically for when repeated values should only count once.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT SUM(DISTINCT salary) AS sum_of_unique_salaries
FROM employees;

The NULL Result Quirk: SUM() of Zero Rows

This is the detail that catches people off guard: COUNT(*) on zero matching rows returns 0, a real number. SUM() (and AVG(), MIN(), MAX()) on zero matching rows returns NULL, nothing at all, not zero. An application that does total + 100 on a NULL SUM() result gets NULL back, not 100, exactly the NULL-propagation behavior covered back in the SQL NULL Values lesson.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT SUM(salary) AS total
FROM employees
WHERE department = 'Marketing';
-- If no employees are in Marketing, this returns NULL, not 0.

NOTE

If a query needs 0 instead of NULL when there might be no matching rows, wrap it: COALESCE(SUM(salary), 0), covered in Module 6's NULL functions lesson. Not needed yet, just worth knowing this exists for when it comes up.

Try It Yourself

Write a query that returns the total salary of active employees in the Research department.

Answer

SELECT SUM(salary) AS research_active_payroll
FROM employees
WHERE department = 'Research' AND status = 'active';

Frequently Asked Questions

Does SUM() skip NULL values within the rows it's summing, the same way COUNT(column) does?

Yes. If salary is NULL for one employee, that row is excluded from the total entirely, treated as if it weren't there, not as a zero contribution.

Can SUM() be used on more than one column at once, like SUM(salary, bonus)?

No, SUM() takes exactly one column (or expression). To total two columns combined, add them first: SUM(salary + bonus) sums the row-by-row total of both columns across all rows.

Common Beginner Mistakes

Assuming SUM() returns 0 when nothing matches

It returns NULL, not 0, when the WHERE clause matches zero rows (or when every value in the column is NULL). This is different from COUNT(*), which correctly returns 0 for zero matching rows, since COUNT(*) is counting rows, not summing values.

Trying SUM() on a text or date column

SUM() requires a numeric column; attempting it on department or hire_date raises an error on every database in this course. MIN() and MAX() work on those types, SUM() and AVG() don't.

Best Practices

Only use SUM() on genuinely numeric columns.
Remember SUM() can return NULL, not 0, when nothing matches, handle that case in application code or with COALESCE() if a guaranteed number is needed.
Reach for SUM(DISTINCT column) only when repeated values genuinely should count once, it's the exception, not the default.

Interview Questions

What does SELECT SUM(salary) FROM employees WHERE department = 'Legal' return if the Legal department doesn't exist in the table?

NULL, not 0 and not an error. WHERE matches zero rows, and SUM() of zero rows is NULL, the same NULL-propagation rule that applies whenever an aggregate function has nothing to aggregate (except COUNT(*), which returns 0 for zero rows since it's counting rows rather than summing values).

What's the difference between SUM(salary) and SUM(DISTINCT salary)?

SUM(salary) adds every employee's salary, including duplicates, if two employees both earn 90000, both values count. SUM(DISTINCT salary) adds only the unique salary values present in the column, so that same 90000 would only be counted once no matter how many employees share it.

Summary

SUM() totals a numeric column, ignoring NULLs within it, and SUM(DISTINCT column) totals only unique values. The detail most worth remembering: SUM() returns NULL, not 0, when there's nothing to sum, different from COUNT(*)'s behavior on zero rows.

What's Next?

The next lesson covers AVG(), the last of the five core aggregate functions, and how it relates mathematically to SUM() and COUNT().

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.