Row By Row Summing of all previous rows?

Question

Post reply

Row By Row Summing of all previous rows?

Alasdair Thomson

Hall of Fame

Points: 3630
More actions
March 18, 2009 at 3:14 am

#131839

Hi all, i have currently a sp which returns the following;
ChapterPageTitle No.OfPages
---------------- -----------
Chapter 1 2
Chapter 2 4
Chapter 3 1
Chapter 4 2
What I want the sp to return is the following;
ChapterPageTitle No.OfPages PageNumber
---------------- ----------- ------------
Chapter 1 2 1
Chapter 2 4 3
Chapter 3 1 7
Chapter 4 2 8
So I am after a field that calculates the following, if this is the first row then return 1, then to add the value in the above row from the fields PageNumber and No.OfPages.
I'm sure this can be done but how?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ashok.S SSC Eights! Points: 869 More actions · Answer 1

Hi Thompson,

Hope this code will help you in getting the result.

CREATE TABLE #TEMPCHAPTER

(

CHAPTERID INT

,CHAPTERNAME VARCHAR(100)

,NO_OF_PAGES INT

)

INSERT INTO #TEMPCHAPTER

SELECT 1,'CHAPTER1',2

UNION ALL

SELECT 2,'CHAPTER2',4

UNION ALL

SELECT 3,'CHAPTER3',1

UNION ALL

SELECT 4,'CHAPTER4',2

SELECT CHAPTERID, CHAPTERNAME, NO_OF_PAGES FROM #TEMPCHAPTER

ORDER BY CHAPTERNAME

SELECT

TEMP1.CHAPTERID

,TEMP1.CHAPTERNAME

,TEMP1.NO_OF_PAGES

,SUM(TEMP2.NO_OF_PAGES) - TEMP1.NO_OF_PAGES + 1 AS PAGENUMBER

FROM

#TEMPCHAPTER TEMP1, #TEMPCHAPTER TEMP2

WHERE

TEMP2.CHAPTERID <= TEMP1.CHAPTERID

GROUP BY

TEMP1.CHAPTERID,TEMP1.CHAPTERNAME, TEMP1.NO_OF_PAGES

DROP TABLE #TEMPCHAPTER

Regards,

Ashok S

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Here's another way:

-- sample data

DROP TABLE #temp

CREATE TABLE #temp ([ChapterPageTitle] VARCHAR(9), [No.OfPages] INT)

INSERT INTO #temp ([ChapterPageTitle], [No.OfPages])

SELECT 'Chapter 1', 2 UNION ALL

SELECT 'Chapter 2', 4 UNION ALL

SELECT 'Chapter 3', 1 UNION ALL

SELECT 'Chapter 4', 2

-- query

SELECT a.[ChapterPageTitle], a.[No.OfPages],

[PageNumber] = 1 + ISNULL((SELECT SUM([No.OfPages]) FROM #temp WHERE [ChapterPageTitle] < a.[ChapterPageTitle]), 0)

FROM #temp a

-- results

ChapterPageTitle No.OfPages PageNumber

---------------- ----------- ------------

Chapter 121

Chapter 243

Chapter 317

Chapter 428

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 3

Alasdair Thomson

Hall of Fame

Points: 3630

March 18, 2009 at 9:30 am

#962023

This worked perfectly! Thank you all for your help!