SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary
  FROM Master_Employee
  ORDER BY Department;

How this Works
PARTITION BY Department ORDER BY Sno

Partition By will first Partition by Department.Which means the Cumulative Sum will get Reset once the Department Changes.

ORDER BY Sno will make sure the Cumulative value calculated will not be swapped and stays the same for the respective rows.

Now to have a total amount for the Salary the query is as below.

  SELECT Sno,
       Name,
       Department,
       Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0)
   FROM Master_Employee
  ORDER BY Sno;

Total along with Cumulative Sum

 SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0),
         null
   FROM Master_Employee
  ORDER BY Sno;

Another wat of fetching records using sub query

SELECT Sno,
       Name,
       Department,
       Salary,
       CASE
         WHEN Department = 'Total' THEN
          NULL
         ELSE
          SUM(Salary) over(PARTITION BY Department ORDER BY Sno)
       END AS Cum_Salary
  FROM (SELECT Sno, Name, Department, Salary, null, 1 AS Sort_Order
          FROM Master_Employee
        UNION
        SELECT null,
               null,
               'Total',
               NVL(SUM(Salary), 0),
               null,
               2 AS Sort_Order
          FROM Master_Employee)
 ORDER BY Department, Sno, Sort_Order;

Comments are closed.