SUM Function — Learn SQL

Overview

The SUM function adds up all numeric values in a column or partition.

Learning Objectives

Accumulate numerical columns with SUM().
Use SUM(DISTINCT col) to total unique values.
Handle NULL accumulations safely.

Detailed Concept Explanation

SUM only operates on numeric columns. It aggregates the values in a column, ignoring any NULL elements.

Aggregating Nulls

If all values in a group or column are NULL, the SUM function returns NULL rather than 0. You can wrap the function in COALESCE to default the total to 0 when necessary: COALESCE(SUM(amount), 0)

Code Examples

SQL

sql

-- Calculate total revenue, handling potential NULL sales
SELECT 
  SUM(quantity * unit_price) AS raw_total,
  COALESCE(SUM(tax_amount), 0) AS total_tax
FROM invoice_items;

Best Practices

Zero Defaults: Always use COALESCE(SUM(column), 0) when displaying totals in user interfaces to avoid displaying empty or null values.

Interview Perspective

Warning

Common Pitfall: SUM on NULL Columns

Be careful: if a table contains 0 rows or only contains NULLs in the aggregated column, SUM returns NULL. Always specify a fallback to ensure numeric consistency: SELECT COALESCE(SUM(salary), 0) FROM employees WHERE dept = 'Marketing';

Interactive Challenges

Challenge 1: Sum Total Salaries

Write a query to get the sum of 'salary' (alias 'total_salary') for all employees who work in department ID 3.

Summary

SUM totals numeric fields, ignoring NULL values. Use COALESCE to display 0 when there are no values to sum.

Previous LessonCOUNT Function

Next LessonAVG Function