Average(AVG) analytic function in Oracle SQL

Average(AVG) analytic function in Oracle SQL

AVG – Average function is used to find the avarage value of data. You can use different oracle clause with it to find specific things.
I have using the sample HR schema to show you the example of AVG function.

Example:

1. Find the average salary in your company or from employees tables.
``` SQL> select avg(salary) from employees;```

``` AVG(SALARY) ----------- 6461.83178 ```

2. Check the average salary by department wise we have department id column in employee table.
``` SQL> select department_id,avg(salary) from employees group by department_id order by 1;```

``` DEPARTMENT_ID AVG(SALARY) ------------- ----------- 10 4400 20 9500 30 4150 ```

3. Check average salary of employees in department with analytic style.
``` SET PAGESIZE 50 BREAK ON department_id SKIP 1 DUPLICATES```

``` SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_sal FROM employees; EMPLOYEE_ID DEPARTMENT_ID SALARY AVG_DEPT_SAL ----------- ------------- ---------- ------------ 200 10 4400 4400 201 20 13000 9500 202 20 6000 9500 114 30 11000 4150 115 30 3100 4150 116 30 2900 4150 117 30 2800 4150 118 30 2600 4150 119 30 2500 4150 ```

4. Make Average salary rowwise with department id then you can use Order by clause. see output carefully.
order_by_clause is used to order rows, or siblings, within a partition.
``` SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS avg_dept_sal_sofar FROM employees;```

``` EMPLOYEE_ID DEPARTMENT_ID SALARY AVG_DEPT_SAL_SOFAR ----------- ------------- ---------- ------------------ 200 10 4400 4400 202 20 6000 6000 201 20 13000 9500 119 30 2500 2500 118 30 2600 2550 117 30 2800 2633.33333 116 30 2900 2700 115 30 3100 2780 114 30 11000 4150 ```

5. If you have same value of salary in column then range will give you the last average value, but row_avg will give you row wise average.

Windowing_clause gives some analytic functions a further degree of control over this window within the current partition,
or whole result set if no partitioning clause is used.
RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point

Parameter value defines:
UNBOUNDED PRECEDING : The window starts at the first row of the partition.
UNBOUNDED FOLLOWING : The window ends at the last row of the partition.
CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.
value_expr PRECEDING : A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used with RANGE, it can also be an interval literal if the order_by_clause uses a DATE column.
value_expr FOLLOWING : As above, but an offset after the current row.

``` SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_avg, AVG(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_avg FROM employees;```

``` EMPLOYEE_ID DEPARTMENT_ID SALARY RANGE_AVG ROWS_AVG ----------- ------------- ---------- ---------- ---------- 200 10 4400 4400 4400 202 20 6000 6000 6000 201 20 13000 9500 9500 132 50 2100 2100 2100 136 50 22002166.666 2150 128 50 22002166.666 2166.66 135 50 2400 2260 2225 127 50 2400 2260 2260 140 50 2500 2380 2300 144 50 2500 2380 2328.57143 131 50 2500 2380 2350 191 50 2500 2380 2366.66667 182 50 2500 2380 2380 ```

This site uses Akismet to reduce spam. Learn how your comment data is processed.