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