Query for total should keep on adding with each row of cost Column

Question

Hi

I have table with columns as

      Operation             Cost

     Material Issue         10 
     Material Return        20
     X                      30
     Y                      40
     Z                      50

I want want a query where columns are

     Operation             Cost    Total

     Material Issue         10      10
     Material Return        20      30
     X                      30      60
     Y                      40      100
     Z                      50      150

ie.., the the total should keep on adding with each row of cost Column

Answer 1

See this post about Running Counts

Answer 2

try this

DECLARE @Table TABLE(
     ID INT IDENTITY(1,1),
     Descr VARCHAR(20),
     Val FLOAT
)

INSERT INTO @Table (Descr,Val) SELECT 'X', 10
INSERT INTO @Table (Descr,Val) SELECT 'Y', 20
INSERT INTO @Table (Descr,Val) SELECT 'Z', 50
INSERT INTO @Table (Descr,Val) SELECT 'A', 75
INSERT INTO @Table (Descr,Val) SELECT 'B', 100

SELECT  t1.Descr,
     t1.Val,
     SUM(ISNULL(t2.Val,0))
FROM    @Table t1 LEFT JOIN
     @Table t2 ON t1.ID >= t2.ID
GROUP BY    t1.Descr,
      t1.Val

Answer 3

There are different ways to calculate running totals. This article on SQLTeam covers them and will help you get your query done. The code examples there are easy adaptable, so I let you choose the one which best fits your needs.