CODEWITHSUNDEEP
sqloracle-database
ejvar
ejvar

Reputation: 39

Query to calculate both cumulative and total SUM over salary

Name  Location  Salary
smith newyork   6000 
adam  dallas    5000
rams  delhi     7000
scott laondon   4000

and output should be like this

Name  Location  Salary Running_salary  total_salary
smith newyork   6000    6000            22000
adam  dallas    5000    11000           22000 
rams  delhi     7000    18000           22000
scott lndon     4000    22000           22000

Upvotes: 1

Views: 4819

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT name,
location,
salary,
SUM(salary) OVER ( ORDER BY name) AS running_Salary,
/* order by name can replaced with rownum or rowid , but has to be some
column for perfect ordering as internal order is not judgeable */
SUM(salary) OVER () AS total_salary
FROM yourtable

SQL Fiddle

Upvotes: 2

Rachcha
Rachcha

Reputation: 8816

WITH cte AS(
SELECT  'smith' AS Name, 'newyork' AS Location, 6000 AS Salary FROM dual
UNION ALL SELECT  'adam', 'dallas', 5000 FROM dual
UNION ALL SELECT  'rams', 'delhi', 7000 FROM dual
UNION ALL SELECT  'scott', 'laondon', 4000  FROM dual
),
derived_cte AS (
SELECT rownum rn, name, location, salary
  FROM cte)
SELECT dc.name, dc.location, dc.salary,
       dc.salary + (select sum(dc_in.salary)
                      from derived_cte dc_in
                     where dc_in.rn < dc.rn) AS running_salary,
       (select sum(salary) from cte) AS total_salary
  FROM derived_cte dc;

OUTPUT:

name    location  salary  running_salary  total_salary
------- --------- ------- --------------  --------------
smith   newyork   6000    22000
adam    dallas    5000    11000           22000
rams    delhi     7000    18000           22000
scott   laondon   4000    22000           22000

Upvotes: 0

Related Questions