How To Use AVG In Oracle SQL

How To Use AVG In Oracle SQL

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba system database management system oracle performance tuning 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 learn sql financial advisor personal financial

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;

dba oracle dba system database management system oracle performance tuning 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 learn sql financial advisor personal financial

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;

dba oracle dba system database management system oracle performance tuning 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 learn sql financial advisor personal financial

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;

dba oracle dba system database management system oracle performance tuning 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 learn sql financial advisor personal financial

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*