Adding integers in a table

Question

Post reply

Adding integers in a table

Mark-459099

SSC Eights!

Points: 911
More actions
December 29, 2008 at 7:48 pm

#195535

Does anyone know how to create an addition column in sql.

For example, I have a column called POINTS. This column stores the total points a user has. How can I take the existing points the person has and add additional points, thereby updating their total points.

For example, I am a user who has 10 points in my points column.

I've created a program where once the user finishes a quiz they get bonus points (say 20 points.) I would like to automatically add the 20 to the 10 so that their data is updated to 30.

Does sql have adding columns where everytime an integer is inserted into a column, it just adds that value to the initial value (i.e., does a rolling tally)?

If not, how can I do this?

Any help will be appreciated, thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

I never try to store a total in a row where the details exist...instead I always use a view that does a sum() of the data in question. Since the view is just a saved select statement, it is ALWAYS correct and leverages the advantages of SQL server.

using that idea, why can you not use a view that does your logic, ie SUM(POINTS) +10 GROUP BY USERID or whatever?

Mark (12/29/2008)

Does anyone know how to create an addition column in sql.

For example, I have a column called POINTS. This column stores the total points a user has. How can I take the existing points the person has and add additional points, thereby updating their total points.

For example, I am a user who has 10 points in my points column.

I've created a program where once the user finishes a quiz they get bonus points (say 20 points.) I would like to automatically add the 20 to the 10 so that their data is updated to 30.

Does sql have adding columns where everytime an integer is inserted into a column, it just adds that value to the initial value (i.e., does a rolling tally)?

If not, how can I do this?

Any help will be appreciated, thanks.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Mark (12/29/2008)

Does anyone know how to create an addition column in sql.

For example, I have a column called POINTS. This column stores the total points a user has. How can I take the existing points the person has and add additional points, thereby updating their total points.

For example, I am a user who has 10 points in my points column.

I've created a program where once the user finishes a quiz they get bonus points (say 20 points.) I would like to automatically add the 20 to the 10 so that their data is updated to 30.

Does sql have adding columns where everytime an integer is inserted into a column, it just adds that value to the initial value (i.e., does a rolling tally)?

If not, how can I do this?

Any help will be appreciated, thanks.

You mean a "running total"... it's one of the most difficult things to do in SQL Server with any performance in mind... a view with a triangular join will do it, but it's gonna get real slow in a hurry. And, even though a cursor would be fairly fast for this, it pales in comparison to the method in the following article...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

--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)
Intro to Tally Tables and Functions

mithun.gite SSCrazy Points: 2333 More actions · Answer 3

mithun.gite

SSCrazy

Points: 2333

December 30, 2008 at 1:32 am

#918843

hi,

y dnt u try with the trigeers like

create trigger t1

on quiz

for update

as

declare @id int,@t1 numeric,@t2 numeric

select @id=user_id1,@t1=points from inserted

select @t2=points from quiz where user_id1=@id

update quiz set points=@t1+@t2 where user_id1=@id

Manish Khattar Old Hand Points: 389 More actions · Answer 4

Manish Khattar

Old Hand

Points: 389

December 30, 2008 at 3:32 am

#918899

Well you can use a simple update as:

UPDATE BONUS SET POINTS=POINTS+@BONUSPOINTS WHERE UID=@UID

Although this requires change in query you can use INSTEAD OF triggers to accomodate the change.

r, manish

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

Mark (12/29/2008)

Does anyone know how to create an addition column in sql.

For example, I have a column called POINTS. This column stores the total points a user has. How can I take the existing points the person has and add additional points, thereby updating their total points.

For example, I am a user who has 10 points in my points column.

I've created a program where once the user finishes a quiz they get bonus points (say 20 points.) I would like to automatically add the 20 to the 10 so that their data is updated to 30.

Does sql have adding columns where everytime an integer is inserted into a column, it just adds that value to the initial value (i.e., does a rolling tally)?

If not, how can I do this?

Any help will be appreciated, thanks.

Mark, you need a new table called, say, UserPoints, which holds a user ID, some kind of identifier of the transaction in question such as quiz ID, perhaps the datetime, and the points awarded for the transaction. Each time a user is awarded points, you create a new row in this table. The total number of points held by a user is obtained by summing the points column in the UserPoints table for that user.

Cheers

ChrisM

^{“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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Chris Morris (12/30/2008)

Mark (12/29/2008)

Does anyone know how to create an addition column in sql.

For example, I have a column called POINTS. This column stores the total points a user has. How can I take the existing points the person has and add additional points, thereby updating their total points.

For example, I am a user who has 10 points in my points column.

I've created a program where once the user finishes a quiz they get bonus points (say 20 points.) I would like to automatically add the 20 to the 10 so that their data is updated to 30.

Does sql have adding columns where everytime an integer is inserted into a column, it just adds that value to the initial value (i.e., does a rolling tally)?

If not, how can I do this?

Any help will be appreciated, thanks.

Mark, you need a new table called, say, UserPoints, which holds a user ID, some kind of identifier of the transaction in question such as quiz ID, perhaps the datetime, and the points awarded for the transaction. Each time a user is awarded points, you create a new row in this table. The total number of points held by a user is obtained by summing the points column in the UserPoints table for that user.

Cheers

ChrisM

furthering this a bit...

after the new row is created you can update the points column in the main table by adding the "new" points to "existing" points. This way you wont have to do a SUM everytime you want to check a users points. You can get the details from the new (UserPoints) table.

"Keep Trying"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Heh... so which is it that you actually want? A running total, or a current balance for new rows... there is a slight 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)
Intro to Tally Tables and Functions

jhadden SSCrazy Points: 2656 More actions · Answer 8

Mark,

Best practices would dictate a separate POINTS table that held a row for each time points were added (or subtracted). This accomplishes two things. Normalized schema and an audit log of what activity was involved in the total points calculation. As was stated before, then your query need only to SUM the values of this table for the specified user. As a general normalization rule, you should store the raw data and calculate values from the raw data at the time that it is needed. Creating a running total within a row is a bad idea since there is no underlying context to the number that your presenting and no way of knowing if it is correct as there is no audit trail.

Jeff