Cumulative Addition

Question

Cumulative Addition

Meera

SSCommitted

Points: 1966
More actions
July 13, 2008 at 10:59 pm
Go to Answer
#184606

Dear Respected Members!
i need a help regarding a tough approach.
Here is my query's result,
-------------------------------------------------
Date --- Name -- Col1 -- Col2 --References
--------------------------------------------------
21/08/07-- Customer1--(1000)--( )---Ref1
21/08/07-- Customer2--( ) --(1000)---Ref10
22/08/07-- Customer3--( ) --(1000)---Ref4
i wish to add another column like the following,
-------------------------------------------------
Date --- Name -- Col1 -- Col2 --References-- Total
--------------------------------------------------
21/08/07-- Customer1--(1000)--( )---Ref1------- (-1000)
21/08/07-- Customer2--( ) --(1000)---Ref10----- 0
22/08/07-- Customer3--( ) --(1000)---Ref4 ------1000
ie a cumulative addition of col1 & col2. The amt in col1 must be detected and the amt in col2 must be added in the last col.
is it possible via query?
advance Thanks,
Dhana.
Jeff Moden

SSC Guru

Points: 1004366
More actions
July 14, 2008 at 6:27 am
Answer
#842323

That's called a "Running Total". See the following URL for one of the better ways to do that.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

A_A_R_T SSC Eights! Points: 900 More actions · Answer 1

So you want a per row accumulation of Col1 and Col2 and put the answer in Total? (I'm having a bit of a problem with some smilies in your tables ;))

You can create a computed column for this. A computed column automatically 'computes' the value of your column. So you'll set your 'Total' column to be the accumulative of your 'Col1' and 'Col2'.

Meera SSCommitted Points: 1966 More actions · Answer 2

Hi Rookie! Thks for ur reply. But shall i know wat is computed columns?

how to bring into the query can you guide Plz?

Dont know how the smileys came there over my typings.

Thanks,

A_A_R_T SSC Eights! Points: 900 More actions · Answer 3

Hi!

Have you clicked on the link I provided about computed columns?

When using computed columns you don't need to use a query to accumulate the columns. The accumulation is done by db design.

When you open your db in SSMS you do a rightclick on the table you want to modify and then click 'modify'. Select the 'Total' column. In the 'Column properties' you go to the 'Computed column specification' and enter the expression you'd like. See my example code:

-- Create test table.

create table test

(

field1 int,

field2 int,

total int

)

-- Now go to SSMS and make a computed field of 'total' by putting in this

--expression '([field1] + [field2])' in the computed column specification.

-- Next: fill the table with some data.

insert into test (field1,field2)

values ('2','6')

-- Select and you'll see...

select * from test

Meera SSCommitted Points: 1966 More actions · Answer 4

Rookie! Thks for ur quick response,

But the computed columns u say is nothing but similar to a calculated field. instead of goin to such cols we can just add another col with the formula of wat we need. and again i am trying this in a report presentation.

the thing hard here is to catch the previous row's value in order to calculate with the current row value. i am unable to see such features in computed columns.

Can u help me ?

Thank you,