December 29, 2008 at 7:48 pm
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.
December 29, 2008 at 8:19 pm
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
December 29, 2008 at 8:53 pm
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
Change is inevitable... Change for the better is not.
December 30, 2008 at 1:32 am
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
December 30, 2008 at 3:32 am
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
December 30, 2008 at 3:55 am
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
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
December 30, 2008 at 4:04 am
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"
December 30, 2008 at 4:42 am
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
Change is inevitable... Change for the better is not.
December 30, 2008 at 2:08 pm
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