Click here to Skip to main content
13,139,254 members (89,090 online)
Click here to Skip to main content
Add your own
alternative version

Stats

32.4K views
14 bookmarked
Posted 19 Jun 2009

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.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
hfrmobile4-Sep-09 0:39
memberhfrmobile4-Sep-09 0:39 
GeneralMy vote of 1 Pin
VMykyt16-Jul-09 20:46
memberVMykyt16-Jul-09 20:46 
GeneralSimilar variation Pin
Muhammad Mazhar24-Jun-09 7:14
memberMuhammad Mazhar24-Jun-09 7:14 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 20 Jun 2009
Article Copyright 2009 by S.Vinothkumar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid