ORACLE AVG

In this article, we will be learning about the AVG aggregate function in Oracle.

AVG is a group function as it works on a group of rows and is used for calculating the average value.

Oracle AVG syntax

SELECT AVG(expression)
FROM table
[WHERE conditions];

Here,

expression – It could be a list of values or a column name.

The AVG function returns the average of all the values we provide. We can use DISTINCT or ALL before our expression, to instruct Oracle to return average of all values or just the unique values in the list or column.

For example, AVG of 1,2,3,3 is 1 + 2+ 3+ 3 / 4 = 2.25. By default, AVG function calculates average of all values. If we want average of just unique values then we write DISTINCT before the expression, now the function calculates average in the following manner: 1 + 2 + 3/ 3 = 2.

The AVG functions omits the NULL values while calculating the average.

AVG Function Examples

We will be using Employee Table from HR Schema for this example:

1. AVG Basic Example on a Column

Suppose we want to calculate the average salary of all employees belonging to department 60, then we use the following query:

SELECT AVG(salary) AS "Average Salary of Employees"
FROM employees
WHERE department_id= 60;

Result:

The query calculates the average of all values in the salary column where department_id = 60. It omits the NULL values while arriving at the final result.

2. AVG with DISTINCT Clause

If we want to calculate the average of unique values values present in salary column, then we will use DISTINCT clause before salary column. Let’s see this query:

SELECT AVG( DISTINCT salary) AS "Average Salary of Employees"
FROM employees
WHERE department_id= 60;

Result:

The query this time returns only the average of ‘unique’ values in salary column belonging to department_id =60. We can also see the difference in result from the above query, as all the repeating values in salary column are omitted.

3. AVG with GROUP BY and ORDER BY Clause

Let’s see one query where we use AVG aggregate function with GROUP BY and ORDER BY Clause.

SELECT department_id, ROUND(AVG( salary),2) AS "Average Salary"
FROM employees
GROUP BY department_id
ORDER BY department_id;

Result:

The query returns the rounded average of salary up till 2 decimal points from each department. We group and order the average of salary based on department_id.

4. AVG with GROUP BY and HAVING Clause

Sometimes we need to use HAVING Clause with GROUP BY to display specific information that we want, lets see one query to understand this example:

SELECT department_id, ROUND(AVG( salary),2) AS "Average Salary"
FROM employees
GROUP BY department_id
HAVING ROUND(AVG( salary),2) > 7500
ORDER BY department_id;

Result:

The query returns the average salary in each department where average salary is greater than 7500. As we can see from results, some departments were eliminated from results as their average salary was less than 7500.

5. AVG with Subquery

Subqueries are the queries within query. For this example, we will calculate the average of average salary across each department in employee table.

SELECT
ROUND( AVG( Average_Salary ), 2) "Average Across Departments"
FROM
(SELECT department_id, ROUND(AVG( salary),2) AS Average_Salary
FROM employees
GROUP BY department_id
);

Result:

The query returns the average of average salary across each department. We use ROUND function to round off the digits to just two.

6. AVG With NULL

For this example,we will be using the following Friends table that we are about to create:

Here’s the query to create this simple table:

CREATE TABLE friends(
friend_id NUMBER,
name VARCHAR2( 255 ),
age NUMBER,
PRIMARY KEY (friend_id)
);

Let’s insert some values into it and we intentionally keep the age of Rachel as NULL.

INSERT INTO friends( friend_id, name, age)
VALUES(1,'Ross',25);
INSERT INTO friends( friend_id, name, age)
VALUES(2,'Phoebe',26);

INSERT INTO friends( friend_id, name, age)
VALUES(3,'Monica',24);

INSERT INTO friends( friend_id, name, age)
VALUES(4,'Joey',25);

INSERT INTO friends( friend_id, name, age)
VALUES(5,'Chandler',26);

INSERT INTO friends( friend_id, name, age)
VALUES(6,'Rachel');

We use the following query to calculate the average age of friends.

SELECT
AVG( age)
FROM
friends;

Result:

As we can see from result, the AVG function omitted the NULL value while calculating this average.

Let’s substitute Rachel’s Age = 24 instead of NULL and calculate the average:

We can see the difference in result.

7. Oracle AVG with NVL Function

AVG function can also be used with NVL function which helps in replacing NVL with some other value.

Let’s see the above query by using the NVL function for substituting NULL with zero.

SELECT
AVG( NVL( age, 0 ))
FROM
friends;

Result:

The query returns the average age as 21 which is lower than our previous results as we calculated average by substituting zero in place of NULL.

In this article, we learned about the Oracle AVG function and how we can use it to calculate average of values.