dudushmaya
32 answers
63 thumbs up

Ordinary People Discovering Extraordinary Abilities

Not So Smart Builder

How do I write a select query in SQL Server 2005 ...

How do I write a select query in SQL Server 2005 that calculates a value with the previous record value?

I have a select that returns a number. I would like to have another field that represents the current number + the previous record number.

Any suggestions?


Answer this question
  • # Posted 2 years ago
  • 1019 views
Share Send to a friend Watch Report
 

Best Answer

 

I think you can run the following query:

select

name,

(select sum(value) from TABLENAME tn2 where

tn2.name <= tn.name) as sumValue

from TABLENAME tn

order by name

try this and let me know
Posted 2 years ago ( permalink )
In reply to dudushmaya's answer
gliksman was invited by Yedda to answer this question.

Rated as
Best Answer
0
4
  Report Abuse  

Helpful?

line
line
line


 

All Answers

Order by
 

a good way to do this would be to create an inner join of the table to itself in the following syntax:

select a1.value, a2.value

from tablename a1

inner join tablename a2

on a1.commonfield=a2.commonfield

where a1.id < a2.id

for a more specific answer i will need to understand the table structure and some sample data in it

Hope this helps

Ohad
Posted 2 years ago ( permalink )
In reply to dudushmaya's question
gliksman was invited by Yedda to answer this question.

Rated as
#2 out of 9
0
0
  Report Abuse  

Helpful?

line
line
line



 
dudushmaya
32 answers
63 thumbs up

Ordinary People Discovering Extraordinary Abilities

Not So Smart Builder

Thanks for the reply. I did not understand the query.

 Here's some sample data:

NameValuea3b7c2

On this table I would like to perform a select that will return:

NameTotala3b10c12

Where total is the sum of the current field value with the previous record value.

Thanks.
Posted 2 years ago ( permalink )
In reply to gliksman's answer
Rated as
#3 out of 9
0
0
  Report Abuse  

Helpful?

line
line
line



 

can there be more then 1 previous records?

must there be a previous record?

Please reply with the table's full description and I will try to help you
Posted 2 years ago ( permalink )
In reply to dudushmaya's answer
gliksman was invited by Yedda to answer this question.

Rated as
#4 out of 9
0
0
  Report Abuse  

Helpful?

line
line
line



 
dudushmaya
32 answers
63 thumbs up

Ordinary People Discovering Extraordinary Abilities

Not So Smart Builder

I see that the <table> tag is not working when viewing the post...

Here's a sample data (image):

Data
Posted 2 years ago ( permalink )
In reply to gliksman's answer
Rated as
#5 out of 9
0
0