AVG returns the arithmetic mean of the values in a group. It looks simple, but it divides by the number of non-NULL values, not by the number of rows, so a column with NULLs can give an average that is not the one you meant. In Oracle AI Database 26ai it also averages intervals, such as flight durations.
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:
avg([distinct | all] expr)
AVG(expr) is SUM(expr) / COUNT(expr): both skip NULLs. DISTINCT averages each different value once.
Average Salary
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 34000The average salary is 14,607.27, rounded to two decimals with ROUND, since AVG returns as many decimals as the division produces.
NULLs Change the Answer
Only 7 of 55 employees have a commission. AVG(commission_pct) is the average of those seven; AVG(NVL(commission_pct, 0)) the average across everyone.
Example:
-- AVG divides by the number of non-NULL values
select round(avg(commission_pct), 4) as avg_of_commissioned,
round(avg(nvl(commission_pct, 0)), 4) as avg_of_everyone,
round(avg(distinct salary)) as avg_distinct_salary,
round(avg(salary)) as avg_salary
from employees;Output:
AVG_OF_COMMISSIONED AVG_OF_EVERYONE AVG_DISTINCT_SALARY AVG_SALARY
______________________ __________________ ______________________ _____________
0.0843 0.0107 14494 146078.43% versus 1.07%: two correct answers to two different questions. AVG(DISTINCT salary) treats each different salary once, which gives yet another number.
Average Intervals
AVG of an interval returns an interval, here the average flight time on two routes.
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 average flight from Dubai to London lasted 6 hours, 41 minutes, and 33 seconds.
Things to Know
- A few very large values pull the average up; MEDIAN is more robust when data is skewed.
- AVG over no rows, or only NULLs, returns NULL.
- As an analytic function, AVG(expr) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW) gives moving averages.
Related Guides
Conclusion
AVG returns the mean of the non-NULL values of a group, and of intervals in Oracle AI Database 26ai. Decide whether NULLs should be skipped or counted as zero, round the result for display, and prefer MEDIAN for skewed data.
