How To Use Oracle AVG Function
  Home
 

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;

online tech support online computer help computer technician computer problems computer oracle avg oracle avg in oracle sql avg oracle sql avg in oracle sql database oracle average oracle average in oracle sql average oracle sql average in oracle sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support online computer help computer technician computer problems computer oracle avg oracle avg in oracle sql avg oracle sql avg in oracle sql database oracle average oracle average in oracle sql average oracle sql average in oracle sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support online computer help computer technician computer problems computer oracle avg oracle avg in oracle sql avg oracle sql avg in oracle sql database oracle average oracle average in oracle sql average oracle sql average in oracle sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support online computer help computer technician computer problems computer oracle avg oracle avg in oracle sql avg oracle sql avg in oracle sql database oracle average oracle average in oracle sql average oracle sql average in oracle sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The output above has all set numbers available to see and the common average next to them duplicated over all lines.



See Also:
Home Oracle Select Oracle To_Number Oracle Trunc

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*