## SUM Analytic Functions in Oracle SQL

SUM function is used to find the sum of expression. You can also used in Analytic function.

SUM : aggregate functions are used to calculate the Sum of data.

Syntax:

SUM( expr) [ OVER (partition by | order by) ]

**Example of SUM function**

Calculate the total salary in employee table.

Select sum(salary) from employees

```
```SUM(SALARY)
-----------
688316

Calculate the total salary of each department with GROUP BY Clause.

select department_id,sum(salary) from employees

group by department_id order by 1;

```
```DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 21800
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000

Calculate department wise using analytic function OVER clause:

SELECT department_id,employee_id,

salary,

SUM(salary) OVER (PARTITION BY department_id) AS Total_salary_depart

FROM employees;

```
```DEPARTMENT_ID EMPLOYEE_ID SALARY TOTAL_SALARY_DEPART
------------- ----------- ---------- -------------------
10 200 4400 4400
20 201 13000 19000
20 202 6000 19000
30 114 11000 21800
30 115 0 21800
30 116 2900 21800
30 117 2800 21800
30 118 2600 21800
30 119 2500 21800

Advertisements