ORACLE AVERAGE - Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle AVG function allows finding average number from the number set. The Oracle AVG syntax is following:

AVG (<number>);

The finding average concept 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 total number of numbers in the set.

Let us play the average concept through with the following example. First we do have 5 numbers set and the number are 2, 4, 6, 8 and 10. As on the following SQL query output has.

 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 value is 30.

2 + 4 + 6 + 8 + 10 = 30

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

To get the average do divide the sum with the total number of numbers in the set. The 30 divided by 5 is 6 and the average is 6 as the following illustrations shows:

30 / 5 = 6

The following Oracle AVG function does all the work above within the function and it only returns 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 set numbers available to see and the common average next to them duplicated over all lines.