PL/SQL AVG() Function

The PL/SQL AVG() function serves as a powerful tool for performing aggregate calculations on numeric datasets within a database. By allowing developers to calculate average values while excluding NULL entries, it enhances data analysis capabilities.

In this article, we will explore the AVG() function in detail by covering the syntax, various use cases, and practical examples.

PL/SQL AVG() Function

• In PL/SQL, aggregate functions help to perform summary calculations on datasets and provide an efficient way to extract insights from database tables.
  
• One commonly used function is the AVG() function which allows developers to calculate the average of numeric values excluding NULL entries.
  
• Whether used in financial reports, business analysis, or operational monitoring, it simplifies the task of finding average values in a dataset.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Key Terms

• column_name: The name of the column containing the numeric data.
  
• table_name: The name of the table from which we will retrieve the data.
  
• condition: It is optional, we can specify the condition to filter the rows before calculating the average.

Examples of PL/SQL AVG() Function

• Here CREATE TABLE statement is used to create the table employee with respective columns.
  
• Each INSERT INTO statement adds the new row to the employee table with the values for the employee_id, name, salary and department.

Query:

CREATE TABLE employee (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(20),
    salary NUMBER,
    department VARCHAR2(10)
);


INSERT INTO employee (employee_id, name, salary, department) 
VALUES (1, 'Alice', 5000, 'Sales');

INSERT INTO employee (employee_id, name, salary, department) 
VALUES (2, 'Bob', 6000, 'Sales');

INSERT INTO employee(employee_id, name, salary, department) 
VALUES (3, 'Charlie', 4000, 'IT');

INSERT INTO employee (employee_id, name, salary, department) 
VALUES (4, 'David', 7000, 'IT');

INSERT INTO employee (employee_id, name, salary, department) 
VALUES (5, 'Emma', 5500, 'HR');

Output:

Example 1: AVG() Function with Single Field

In this example, the AVG() function calculates the average of the salary field from the employee table. The result is returned as avg_salary, representing the average salary of all employees, excluding any NULL values in the salary column.

Query:

SELECT AVG(salary) AS avg_salary
FROM employee;

Output:

Explanation:

• Summing Values: 5000 (Alice) + 6000 (Bob) + 4000 (Charlie) + 7000 (David) + 5500 (Emma) = 27,500.
  
• Calculating the Average: The number of values is 5 so the average is calculated as 27,500/5 = 5500.
  
• Hence, the average salary for the all employees values is 5500.

Example 2: AVG() Function Using DISTINCT

In this example, the AVG() function combined with the DISTINCT keyword calculates the average salary after eliminating any duplicate values in the salary field. The result avg_distinct_salary represents the average of only the unique salary values from the employee table.

Query:

SELECT AVG(DISTINCT salary) AS avg_distinct_salary
FROM employee;

Output:

Explanation:

• Identifying Distinct Salaries: The DISTINCT keyword is ensure that the duplicate salary values are excluded from calculation.
  
• The distinct salaries are 5000, 6000, 4000, 7000, 5500.
  
• Summing Distinct Salaries: The sum of distinct salaries is 5000+6000+4000+7000+5500 = 27,500.
  
• Calculating the Average: The number of distinct salary values is 3.
  
• The average is calculated as 27,500/5 = 5500.

Example 3: AVG() Function Using Formula

In this example, the AVG() function is used with a formula to calculate the average salary with a 10% bonus. The expression salary * 1.10 adjusts each salary by 10%, and the AVG() function then calculates the average of these modified values, returning the result as avg_salary_with_bonus.

Query:

SELECT AVG(salary * 1.10) AS avg_salary_with_bonus
FROM employee;

Output:

Explanation:

• Applying the Formula to Each Salary: The AVG() function is used with formula salary * 1.10 to add the 10% bonus to the each employee's salary.

• Summing the Adjusted Salaries: The sum of adjusted salary value is 5500 + 6600 + 4400 + 7700 + 6050 = 30,250.
  
• Calculating the Average: The number of salary values is 5.
  
• The average salary with bonus applied is calculated as 30,250/5 = 6050 .

Example 4: AVG() Function with using GROUP BY clause

In this example, the AVG() function is combined with the GROUP BY clause to calculate the average salary for each department. This query groups the employee data by department and then computes the average salary within each group, returning the results as avg_salary for each department.

Query:

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department;

Output:

Explanation: In the above query, we retrieves the average salary for each department from the employee table. It groups the results by the department column, allowing the AVG(salary) function to calculate the average salary for all employees within each department.

Conclusion

In summary, the PL/SQL AVG() function is instrumental in deriving meaningful insights from numeric data in databases. Its versatility is showcased through various applications, such as calculating overall averages, filtering duplicates, and applying formulas for adjusted values