How To Use AVG In Oracle SQL

This tutorial is based on examples so it would be easier to understand. Oracle AVG function allows to calculate an average number from a number set. The syntax of Oracle AVG is:

AVG (<number>);

The consept of the average calculation is quite simple. For example you have a set of number and first thing is to add all numbers in the set and divide the sum by the amount of numbers in the set.

Let’s play the average concept through with the first example. First we do have a “5” number set and the number are “2“, “4“, “6“, “8” and “10“. As on the following SQL query output has the same.

 SELECT rownum * 2 as my_numbers
   FROM dual
CONNECT BY rownum < 6;

To add all numbers in the set we will use the Oracle SUM function and the output number is “30” as the following calculation shows.

2 + 4 + 6 + 8 + 10 = 30

 SELECT SUM(rownum * 2) as my_sum
   FROM dual
CONNECT BY rownum < 6;

To get the average number of the number set we will divide the sum with the total number of numbers in the set. The “30” divided by “5” is “6” and the average number is “6” as the following calculation shows:

30 / 5 = 6

The following Oracle AVG function does all the work above within the function and returns only 1 line with the average value “6“.

 SELECT AVG(rownum * 2) as my_average
   FROM dual
CONNECT BY rownum < 6;

The Oracle AVG function can be used with Oracle Analytic functions and the difference is we don’t have to group all rows to use the function. It does all the calculations on fly.

 SELECT rownum * 2 as my_number,
        AVG (rownum * 2) OVER () AS my_average
   FROM dual
CONNECT BY rownum < 6;

The output above has all numbers in set available to see and the average number over them all next to them repeated over all lines.

See Also:
Oracle Select Oracle Sum Oracle Trunc Home