Technical Article

Running Total on Single query

,

This query calculates the running Total on Table without using cursors or variables.

--- Create Example Table ---

create table ev_example
(id1 int identity,
 val int,
 run_tot int null)

--- Populate Table

insert ev_example(val) select 100
insert ev_example(val) select 200
insert ev_example(val) select 300
insert ev_example(val) select 400
insert ev_example(val) select 500

---- Display Output

select * from ev_example

/*
id1         val         run_tot     
----------- ----------- ----------- 
1           100         NULL
2           200         NULL
3           300         NULL
4           400         NULL
5           500         NULL

(5 row(s) affected)
*/
----- ## Getting the Running per row Without using of Cursors or Variables ##--

select t1.id1
, t1.val
, case id1 when 1 then val
  else (select sum(val) from ev_example where id1 <= t1.id1-1)+ val
end as ValueSum
from ev_example t1

/*

id1         val         run_tot     
----------- ----------- ----------- 
1           100         100
2           200         300
3           300         600
4           400         1000
5           500         1500

(5 row(s) affected)

*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating