🔢 Oracle AVG() Function Tutorial
In this tutorial, you'll learn how to use the Oracle AVG() function to calculate the average of numeric values from a group of rows in SQL queries.
📌 What is the Oracle AVG() Function?
The AVG() function returns the average value of a numeric column. It is typically used with the GROUP BY clause to calculate averages per group, but it can also be used across an entire result set.
🧮 Syntax
AVG([DISTINCT | ALL] expression)
expression– A numeric expression or column name.DISTINCT– Averages only unique values.ALL– Averages all values including duplicates (default).
📋 Example Without GROUP BY
This example returns the average salary from the entire employees table:
SELECT AVG(salary) AS avg_salary FROM employees;
📊 Example With GROUP BY
This example returns the average salary for each department:
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
🔎 Using HAVING with AVG()
You can filter results using HAVING based on average values:
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 10000;
🚫 Handling NULLs
By default, AVG() ignores NULL values. If you want to treat NULLs as 0, use the NVL() function:
SELECT AVG(NVL(salary, 0)) AS avg_salary FROM employees;
📌 DISTINCT vs. ALL
To average only unique values, use DISTINCT:
SELECT AVG(DISTINCT salary) AS avg_distinct FROM employees;
✅ Summary
AVG()calculates the average of numeric values.- Use with or without
GROUP BY. - Ignores
NULLs unless handled withNVL(). - Supports
DISTINCTandALLto control duplicates.
Now you can confidently use the AVG() function in Oracle SQL to compute average values in your datasets.