SUM Function
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
NULLaccumulations 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
-- 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
Summary
SUM totals numeric fields, ignoring NULL values. Use COALESCE to display 0 when there are no values to sum.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.