Introduction I need to write a query for sum the values with the previous row value in current row of a column. So that I have written a single query after a long googled. Here I am trying to give you what I have done. Prerequisite
SQL Server 2005 Description
I have a table with three fields Id, Name and Mark. I have values of all fields like as follows. Id Name Mark --- ----- ----- 1 aaaa 10 2 bbbb 20 3 cccc 30 Now I wants to get the results set of that table like as Id Name Mark --- ----- ----- 1 aaaa 10 2 bbbb 30 3 cccc 60 So I need a single select query to do this performance. For that I have written a query using cross join. Implementation
To fetch the above result set I have written the code as follows by using the cross join. Just use the following code snippet to get the result set of sum of the previous rows. {codecitation class="brush:sql; gutter: true;" width="500px"} select a.Id, a.Name, sum(b.Mark) as Mark from Marks a cross join Marks b where b.Id <= a.Id group by a.Id, a.Name {/codecitation} Code Snippets for Table and Query Table Name: Marks {codecitation class="brush: sql; gutter: true;" width="500px"} CREATE TABLE [dbo].[Marks]( [Id] [bigint] NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Mark] [bigint] NOT NULL ) ON [PRIMARY] select * from Marks select a.Id, a.Name, sum(b.Mark) as Mark from Marks a cross join Marks b where b.Id <= a.Id group by a.Id, a.Name {/codecitation} Conclusion Hence we have done the fetching the result set for sum of the previous rows in sql server. More information here
Thank you
S.Vinothkumar http://vinothnat.blogspot.com |