Click here to Skip to main content
Click here to Skip to main content

Sum of previous row values in SqlServer

, 19 Jun 2009
Rate this:
Please Sign up or sign in to vote.
Sum of previous row values in SqlServer

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.
 

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 

Code Snippets for Table and Query

Table Name: Marks
 

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

mark1.JPG 

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

mark2.JPG 

Conclusion 

Hence we have done the fetching the result set for sum of the previous rows in sql server.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

S.Vinothkumar
Web Developer
India India
Hi Viewers,

I wish to all. This is Vinoth. This is where I try to condense everything that you need to know about me.
 
Blog:
 
visit my blog
 
Interests:

I'm passionate about a great many things and continually learning about the things that interest me. They are wearable computers, User Interface Design, Artificial life, Industrial music.
 

 

 

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberhfrmobile4-Sep-09 0:39 
GeneralMy vote of 1 PinmemberVMykyt16-Jul-09 20:46 
GeneralSimilar variation PinmemberMuhammad Mazhar24-Jun-09 7:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 20 Jun 2009
Article Copyright 2009 by S.Vinothkumar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid