Adding integers in a table

  • 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.

  • 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!

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

  • 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

  • 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

  • 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

  • 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"

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

  • 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

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

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