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