beginner

SUM Function

By DataPlayArena Technical Review BoardUpdated 2026-07-234 min readRuntime Verified

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.

DPA

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.