Cummalitive addition

Question

Post reply

Cummalitive addition

Ananth-431739

Ten Centuries

Points: 1185
More actions
November 20, 2007 at 3:19 am

#127001

hi
i required an below o/p from an query
col1 col2
1 1
1 2
1 3
1 4
1 5
.
.
.
.
eor
The values of column1 and cilumn2 should be dynamic.

Viewing 13 posts - 1 through 13 (of 13 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

November 20, 2007 at 3:36 am

#752572

Is there a question here, and if so what is it?

John

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 2

I need to generate a cummulative calculation in the query. For example:

there are 2 columns and one column should do the cummulative addition from the query

col1 col2

1 1

1 2

1 3

1 4

.

eof

col2 is doing the cummulative addition means second row of col1 + first row of col2 will give the output of second row of col2. similarly for others rows also.....Can you provide the solution????

karthik M One Orange Chip Points: 29139 More actions · Answer 3

what is you question ? if you explained clearly then you will get some good solution not only from me.

karthik

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

This problem has been discussed a few times, so you may want to look at the common solutions to these.

An example is http://www.sqlservercentral.com/Forums/Topic411513-338-2.aspx

The easiest to find these is if you look for "running totals".

Note, that sometimes it is better to calculate the running totals on the client side. In case of reports usually there is support for running totals in the reporting tools (e.g. RunningValue() in Reporting Services).

In summary, if you use subqueries to find aggregate all the previous values your query will not scale, and will grind to a halt after a few thousand/tens of thousands of rows. Cursors is an option, see the link.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

OP simply wants a running count, Karthick.

--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)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Ananth,

How many rows are you talking about? It will make a difference...

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Jeff,

Gathering Running total only is not my job. Really i am trying to give answers which comes under my knowledge.

for your reference,;)

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

http://www.sqlservercentral.com/Forums/Topic425244-169-1.aspx

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

karthik

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

karthikeyan (11/27/2007)
Jeff,
Gathering Running total only is not my job. Really i am trying to give answers which comes under my knowledge.
for your reference,;)
----------------------------------------------------------------
http://www.sqlservercentral.com/Forums/Topic425244-169-1.aspx
----------------------------------------------------------------

Sounds a bit arrogant, Karthik... perhaps it's the language barrier but you would turn down the opportunity to attack one of the most difficult of all performance problems... running totals?

And, for your reference, you never did explain why SELECT * is not a good practice. 😉

--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)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

Ananth,

Are you all set with your running total problem? Was the link that Andras provided sufficient or do you need something a bit more specific? Just wanna make sure that your problem has been solved.

--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)

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 10

Ananth-431739

Ten Centuries

Points: 1185

November 27, 2007 at 10:48 pm

#754829

Jeff,

I got the solution. ....

Thanks.

karthik M One Orange Chip Points: 29139 More actions · Answer 11

Jeff,

regret , I undertook your word...Running total...mistakenly...

Reason 1:

SELECT * queries are also bad from an application maintenance point of view. If a column is added to a table, the results returned to your application will change in structure. Well programmed applications should be referring to columns by name and shouldn't be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.

Reaon 2:

To reduce the size of worktables select only needed columns from the tables. Avoid using select * unless you need all columns of the tables. This reduces the load on tempdb and the cost of sorting the result tables.

Am i correct ? If i am wrong kindly let me know or if you add some more reasons then it would be truly appreciated.

karthik

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 12

Ananth (11/27/2007)
Jeff,
I got the solution. ....
Thanks.

Thanks, Ananth... Just curious, though... did the solution you get have a "<=" anywhere in the code? If so, you may have a performance problem in the future if the scale of the rows increases.

--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)