SUM adds up the values of a column over a group: a payroll, a total of fares, a distance flown. It ignores NULLs, returns NULL when there is nothing to add, and in Oracle AI Database 26ai it also adds intervals, so total time in the air no longer needs conversion to numbers.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
sum([distinct | all] expr)
expr is a number, or in Oracle AI Database 26ai an INTERVAL. ALL, the default, adds every non-NULL value; DISTINCT adds each different value once.
Totals per Group
The first query totals the payroll of the company; the second of each department.
Example:
select count(*) as employees,
count(commission_pct) as with_commission,
count(distinct department_id) as departments,
sum(salary) as payroll,
round(avg(salary), 2) as avg_salary,
min(hire_date) as first_hire,
max(hire_date) as last_hire
from employees;
select department_id, count(*) as staff, sum(salary) as payroll, max(salary) as top_salary
from employees
group by department_id
order by department_id
fetch first 4 rows only;Output:
EMPLOYEES WITH_COMMISSION DEPARTMENTS PAYROLL AVG_SALARY FIRST_HIRE LAST_HIRE
____________ __________________ ______________ __________ _____________ ______________ ______________
55 7 10 803400 14607.27 01-MAR-2012 02-FEB-2026
DEPARTMENT_ID STAFF PAYROLL TOP_SALARY
________________ ________ __________ _____________
10 2 84000 48000
20 12 213800 27000
30 11 99100 22000
40 10 142100 34000DISTINCT and No Rows
SUM(DISTINCT salary) adds each different salary once, so two people on the same salary count once, rarely what you want for money. Over no rows, SUM returns NULL, not 0; wrap it in NVL when a report should show 0.
Example:
select sum(salary) as payroll,
sum(distinct salary) as sum_of_distinct_salaries,
nvl(sum(case when department_id = 999 then salary end), 0) as no_rows_as_zero
from employees;Output:
PAYROLL SUM_OF_DISTINCT_SALARIES NO_ROWS_AS_ZERO
__________ ___________________________ __________________
803400 768200 0The third column applies SUM to a CASE that matches no rows, standing for an empty group, and NVL turns the NULL into 0.
Sum Intervals
In Oracle AI Database 26ai, SUM adds INTERVAL values. Subtracting two timestamps gives an interval, so the total time in the air of a route's flights is a single SUM.
Example:
select r.origin || '-' || r.destination as route, count(*) as flights,
sum(f.actual_arrival - f.actual_departure) as time_in_air,
avg(f.actual_arrival - f.actual_departure) as average_flight,
max(f.actual_departure - f.scheduled_departure) as worst_delay
from flights f join routes r on r.route_id = f.route_id
where f.status = 'ARRIVED' and r.origin = 'DXB' and r.destination in ('LHR', 'SYD')
group by r.origin, r.destination;Output:
ROUTE FLIGHTS TIME_IN_AIR AVERAGE_FLIGHT WORST_DELAY __________ __________ ______________________ _________________________ ______________________ DXB-LHR 72 +20 01:52:00.000000 +00 06:41:33.333333333 +00 01:24:00.000000 DXB-SYD 30 +17 13:30:00.000000 +00 14:03:00.000000 +00 01:18:00.000000
The 72 flights from Dubai to London spent 20 days, 1 hour, and 52 minutes in the air.
Things to Know
- SUM ignores NULL values; a column with some NULLs adds up the rest.
- Use SUM(expr) FILTER (WHERE condition) or SUM(CASE WHEN ... THEN expr END) to total part of the rows.
- As an analytic function, SUM(expr) OVER (ORDER BY ...) gives running totals.
Related Guides
Conclusion
SUM adds the non-NULL values of a group, returns NULL for an empty group, and in Oracle AI Database 26ai adds intervals as well as numbers. Use NVL for a zero total and avoid DISTINCT for amounts.
