SUM Analytic Functions in Oracle SQL

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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