Need Query for calculation total

Question

Need Query for calculation total

vignesh.ms

Hall of Fame

Points: 3456
More actions
May 20, 2014 at 3:24 am

#293472

Hi there,
here is the sample
create table Sample (id int ,value int,total int)
insert into Sample (id,value) values (1,1)
insert into Sample (id,value) values (1,4)
insert into Sample (id,value) values (1,32)
insert into Sample (id,value) values (2,1)
insert into Sample (id,value) values (2,4)
insert into Sample (id,value) values (2,2)
insert into Sample (id,value) values (2,4)
As per I googled this is the running total problem..
I need running total of value group by id..

Viewing 6 posts - 1 through 5 (of 5 total)

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

yuvipoy SSChampion Points: 10469 More actions · Answer 1

vignesh.ms (5/20/2014)
Hi there,
here is the sample
create table Sample (id int ,value int,total int)
insert into Sample (id,value) values (1,1)
insert into Sample (id,value) values (1,4)
insert into Sample (id,value) values (1,32)
insert into Sample (id,value) values (2,1)
insert into Sample (id,value) values (2,4)
insert into Sample (id,value) values (2,2)
insert into Sample (id,value) values (2,4)
I need following out put

Can you tell how the total came for?

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

yuvipoy (5/20/2014)
vignesh.ms (5/20/2014)
Hi there,
here is the sample
create table Sample (id int ,value int,total int)
insert into Sample (id,value) values (1,1)
insert into Sample (id,value) values (1,4)
insert into Sample (id,value) values (1,32)
insert into Sample (id,value) values (2,1)
insert into Sample (id,value) values (2,4)
insert into Sample (id,value) values (2,2)
insert into Sample (id,value) values (2,4)
I need following out put
Can you tell how the total came for?

http://www.sqlservercentral.com/Forums/Attachment15556.aspx%5B/img%5D

yuvipoy SSChampion Points: 10469 More actions · Answer 3

The pic is not clear can u tell in select statement

like 1+4 and so on....

since id 1 is not clear.

for 2 it is like ?

Select 1+4=5

Select 4+2=6

Select 2+4=6

Select 4+0=4

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

yuvipoy (5/20/2014)
The pic is not clear can u tell in select statement
like 1+4 and so on....
since id 1 is not clear.
for 2 it is like ?
Select 1+4=5
Select 4+2=6
Select 2+4=6
Select 4+0=4

Yes you were right ... that is what I expect for both id's...

sorry I was mistakenly posted the output.

Neil Burton SSC-Insane Points: 23000 More actions · Answer 5

I came up against this recently and I was directed here[/url]. Don't be put off by the length of the article, the actual code it uses is very simple. However you need to make sure that you read the article and follow the rules it mentions. You can test the results with this:

create table #Sample (id int ,value int,total int)

insert into #Sample (id,value) values (1,1)

insert into #Sample (id,value) values (1,4)

insert into #Sample (id,value) values (1,32)

insert into #Sample (id,value) values (2,1)

insert into #Sample (id,value) values (2,4)

insert into #Sample (id,value) values (2,2)

insert into #Sample (id,value) values (2,4)

;

with pre as (select id,value, rn = ROW_NUMBER() over (partition by id order by value) from #Sample)

select id, value, x.runtot

from pre p

cross apply(

select runtot = sum(value)

from pre i where i.id = p.id and i.rn <= p.rn

) x

That uses a triangular join and this can have some pretty nasty performance implications but it might be sufficient for what you need, the article will explain better than I can.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537