Today i found new feature added in SQL Server 2012. I was working for a query which was resulting cumulative addition, which can help you in avoiding CTE’s or complex sub queries. This is Possible by using OVER clause.
Let’s Consider an example here:
--Create Table
CREATE TABLE RunTotalTestData (
id int not null identity(1,1) primary key,
Name nvarchar(25),
Department nvarchar(30),
salary int not null
);
-- Inserting Values
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Shekhar','EC',10000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Amit','EC',200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sumit','EC',4000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sunit','EC',7000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sunita','BC',9000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Abhishekh','BC',12000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Shiva','BC',1300000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Niju','CC',1600000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Linju','CC',2200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sreeju','CC',4200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Praveen','CC',5700000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Anju','CC',5800000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Veena','FC',5900000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Parvaty','FC',6000000);
Here is the query to result the cumulative some for each department, In case if we want for all then we can simple use over clause with order by.
SELECT a.id
,a.Name
,a.Department
, SUM(a.salary/100000) OVER (Partition by a.Department ORDER BY a.id) as cumulative_sum
FROM RunTotalTestData a
group by a.id, a.Department,a.Name,a.salary
ORDER BY a.id;
