How to Use Average in SQL Oracle: 5 Simple Examples

The average in SQL Oracle function is an aggregate and analytic function taking an average of a set of numeric value lists. The AVG() function is able to average DISTINCT or ALL values. It can used to calculate the column average or average of some expression. This is similar to a normal average function like taking the sum of column values and dividing by affected row count without taking null rows.

Basic Syntax of the AVG function:

--
SELECT AVG( [DISTINCT | ALL ]column_name/ Expression) AS average FROM table; 
--•	column_name: column name/ Expression for calculate average value. 
--•	table: Table name of column contains.
--

Returns: AVG() This returns Numeric value

Average in SQL Oracle Examples

In the below examples, I would like to add a sales_orders table like the below and do the necessary example according to the query.

--
CREATE TABLE sales_orders (
    order_id VARCHAR2(50) ,
    order_date DATE ,
    discount NUMBER ,
    sales_amount NUMBER,
    product_type VARCHAR2(50),
    PRIMARY KEY(order_id)
);
declare 
  -- insert 100000 records into database
  i integer:=3;
    type namesarray IS VARRAY(10) OF VARCHAR2(50); 
  names namesarray; 
begin
  -- Test statements here
      names := namesarray('Type_1', 'Type_2', 'Type_3', 'Type_4', 'Type_5','Type_6','Type_7','Type_8','Type_9','Type_10');
LOOP
   i:= i+1;
   INSERT INTO sales_orders (order_id, order_date,discount,sales_amount,product_type) 
   VALUES ('So-'||i,Sysdate - round(dbms_random.value(1,400),0),round(dbms_random.value(1,100),0),round(dbms_random.value(1000,100000),0),names(MOD(i,10)+1));
   EXIT WHEN i > 99999;
END LOOP;
end;
--

Example 1: Simple Average Calculation

This is the basic example of the aggregate function of AVG Pass numeric column name into the function and expecting an average of column values.

--
SELECT AVG(sales_amount) AS average_sales FROM sales_orders; 
--

This is basically returning the average sale amount of all the records in the table of sales_orders.

Example 2: Grouped Average Calculation

If you would like to take the average of each group in the dataset then use the AVG function with the required column and also use the group by column to group column.

--
SELECT product_type AVG(sales_amount) AS average_sales 
FROM sales_orders 
GROUP BY product_type; 
--

Average in SQL Oracle returns the average of each group in this example

Example 3: Using Expressions

Average in SQL Oracle is not only for taking the average for a single column but we can have an expression with AVG function. If we would like to do some calculation and take the average of that value then the Oracle AVG function support it as well.

--
SELECT  product_type,round(AVG(discount/sales_amount*100),2)||'%' AS average_sales 
FROM sales_orders 
GROUP BY product_type; 
--

This will return the presentation of the discount amount over the sales amount by each group.

Example 4 – Using DISTINCT

As you know distinct keyword returns the non-duplicate value in a specific dataset. According to that AVG function supports for average of distinct values.

--
SELECT AVG(distinct sales_amount) AS average_sales 
FROM sales_orders 
GROUP BY product_type; 
--

In this case, taking all the distinct sales_amount for average. That means average in SQL Oracle does not use the same values for average.

Example 5 – Using Average with Having

As you know having clouse is used to filter data set by group. In this example filter all the product types that average sales amount greater than 50500. Therefore we used the average function for HAVING conditions.

--
SELECT product_type,AVG(sales_amount)   FROM sales_orders
GROUP BY product_type
having   AVG(sales_amount) >50500
--

Think if you have a Discount column then if you would like to take the average of the discount. But some products may not have a discount then those values are saved as NULL. The average function automatically ignores NULL rows for average.

Performance Considerations: Performance can be changed according to the data load therefore add the proper index and you can gain optimal performance.

Conclusion

In this article, we explain how to work average in SQL Oracle. AVG calculates the average of the selected dataset and returns the numeric value. The average can be used in different behaviors according to the requirement. See other articles related to Oracle from the right pane.