➕ Oracle SUM() Function Guide
This tutorial explains how to use the Oracle SUM() aggregate function to calculate the total of numeric values. You'll also learn to use it with GROUP BY, HAVING, and handle NULLs.
📌 What is Oracle SUM()?
The SUM() function adds up all numeric values in a column or expression and returns the total. It’s commonly used to calculate totals in reports and analytics.
🧮 Syntax
SUM([DISTINCT] expression)
- expression: Numeric column or calculation.
- DISTINCT: Optional keyword to sum unique values only.
📋 Example 1: Simple SUM
Calculate total salary from employees table:
SELECT SUM(salary) AS total_salary FROM employees;
📋 Example 2: SUM with GROUP BY
Calculate total salary per department:
SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id;
📋 Example 3: SUM with HAVING
Return departments where total salary exceeds 100,000:
SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id HAVING SUM(salary) > 100000;
📋 Example 4: SUM with DISTINCT
Sum only distinct salary values:
SELECT SUM(DISTINCT salary) AS total_unique_salaries FROM employees;
📋 Example 5: Handling NULL Values
SUM() ignores NULLs by default. To treat NULL as zero, use NVL():
SELECT SUM(NVL(commission_pct, 0)) AS total_commission FROM employees;
✅ Summary
SUM()calculates the total of numeric values.- Works with or without
GROUP BY. - Supports
DISTINCTto sum unique values. - Ignores NULLs unless handled with
NVL(). - Use with
HAVINGto filter groups.
You can now use the Oracle SUM() function to easily calculate totals in your SQL queries.