![\"\"](\"http:\/\/codethataint.com\/blog\/wp-content\/uploads\/2016\/02\/Cum-Sum.png\")
<\/p>\n<\/p>\n
\r\nSELECT Sno,\r\n Name,\r\n Department,\r\n Salary,\r\n SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary\r\n FROM Master_Employee\r\n ORDER BY Department;\r\n<\/pre>\n
<\/p>\n
How this Works<\/strong>
\nPARTITION BY Department ORDER BY Sno<\/em><\/p>\nPartition By<\/strong> will first Partition by Department.Which means the Cumulative Sum will get Reset once the Department Changes.<\/p>\n
ORDER BY Sno<\/strong> will make sure the Cumulative value calculated will not be swapped and stays the same for the respective rows.<\/p>\n
Now to have a total amount for the Salary the query is as below.<\/p>\n
\r\n SELECT Sno,\r\n Name,\r\n Department,\r\n Salary \r\n FROM Master_Employee\r\n UNION\r\n SELECT null,\r\n null,\r\n 'Total',\r\n NVL(SUM(Salary), 0)\r\n FROM Master_Employee\r\n ORDER BY Sno;\r\n<\/pre>\n
<\/p>\n
Total along with Cumulative Sum<\/strong><\/p>\n
\r\n SELECT Sno,\r\n Name,\r\n Department,\r\n Salary,\r\n SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary \r\n FROM Master_Employee\r\n UNION\r\n SELECT null,\r\n null,\r\n 'Total',\r\n NVL(SUM(Salary), 0),\r\n null\r\n FROM Master_Employee\r\n ORDER BY Sno;\r\n<\/pre>\n
<\/p>\n
Another wat of fetching records using sub query<\/p>\n
\r\nSELECT Sno,\r\n Name,\r\n Department,\r\n Salary,\r\n CASE\r\n WHEN Department = 'Total' THEN\r\n NULL\r\n ELSE\r\n SUM(Salary) over(PARTITION BY Department ORDER BY Sno)\r\n END AS Cum_Salary\r\n FROM (SELECT Sno, Name, Department, Salary, null, 1 AS Sort_Order\r\n FROM Master_Employee\r\n UNION\r\n SELECT null,\r\n null,\r\n 'Total',\r\n NVL(SUM(Salary), 0),\r\n null,\r\n 2 AS Sort_Order\r\n FROM Master_Employee)\r\n ORDER BY Department, Sno, Sort_Order;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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… Continue reading