Query to find running total using windows analytical functions
/* Create global temporary table */
CREATE TABLE ##Employee_Temp(EmpId INT,EMPName VARCHAR(100), Salary MONEY,DEPTID INT,LOC VARCHAR(200))
/* Insert Sample Data in global temporary table */
INSERT INTO ##Employee_Temp VALUES
(1,'RAM',10000,1,'CHARLOTTE'),
(2,'GEORGE',11000,1,'CHARLOTTE'),
(3,'BENJAMIN',9000,2,'CHARLOTTE'),
(4,'MARK',12000,2,'NEW YORK'),
(5,'ALLEN',13000,3,'NEW YORK'),
(6,'NILOLA',14000,3,'NEW YORK'),
(7,'REDIFF',20000,4,'DELHI'),
(8,'SHAYAM',16000,4,'DELHI'),
(9,'ALLARD',17000,4,'LONDON'),
(10,'KEVIN',18000,5,'TOKIO')
/* Below is the select Query to get running total from top to bottom and bottom to top */
select *,
SUM(Salary ) OVER( ORDER BY EMPID ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningTotalUsingROWS_TOPTOBOTTOM,
SUM(Salary ) OVER( ORDER BY EMPID RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningTotalUsingRANGE_TOPTOBOTTOM,
SUM(Salary ) OVER( ORDER BY EMPID ASC ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING ) AS RunningTotalUsingROWS_BOTTOMTOTOP,
SUM(Salary ) OVER( ORDER BY EMPID ASC RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING ) AS RunningTotalUsingRANGE_BOTTOMTOTOP
FROM ##Employee_Temp ORDER BY EMPID ASC
Result:
Running total with selected (3) PRECEDING & FOLLOWING:
Result:
