April 1, 2008 at 2:10 pm
I'm sure this is probably an easy one but I'm still pretty new to a lot of this stuff.
I have a table with 5 columns in it (which looks something like this)
USER_IDVAL_1VAL_2VAL_3VAL_4
000010112
000020211
000030000
000041111
My 2nd table has 2 columns and looks like this.
USER_ID Total
00001
00002
00003
00004
My goal:
I want to take the SUM of VAL_1-VAL_4 for each USER_ID and have the result show in my 2nd table for said user under the total column.
Further, I woudl like it such that if one fo the VAL_? are later modified that it kicks something off to update the total at that time.
Is this something that is fairly easy to do in SQL05 or am I biting off a bit much here for a newb?
Thanks,
Bob
April 1, 2008 at 3:20 pm
You can do it with a View pretty easily:
Create View vwTable1 AS
Select USER_ID, VAL_1 - VAL_4 AS [Total]
From Table1
And it's always up-to-date.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 3:54 pm
Ok, sorry for this but I'm still missing something. Does this view only retrieve the result and how then does this result get in to the appropriate field of the 2nd table.
:w00t:
April 1, 2008 at 5:11 pm
It's pretty simple to update a value in one table with data from another.
Here's the example from SQL Books online:
USE pubs
GO
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date =
(SELECT MAX(sales.ord_date) FROM sales)
GO
In this snippet, you are updating table titles with the values found in sales, but you need to reference both titles and sales tables in your FROM clause. Also, it helps to alias the tables (title t).
If you want the total to update automatically every time a value changes in the first table, you can use a trigger that fires on update. This is also very simple to set up, and you should be able to find examples in SQL Server Books Online or through a search engine.
Hope this helps.
Julia Morton
April 1, 2008 at 6:41 pm
Actually, I think I'm making way more out of this than I need to. Let me try to explain this again as I've changed things a bit.
Lets say I have a table like this...
USER_ID CARS BOATS PLANES T_VEHICLES
1234 2 1 1
1235 1 3 2
2323 3 3 1
So, in the T_vEHICLES column, I want to tally the numbers from the cars, boats and planes columns.
Can this be done via the calculated columns function within the gui? Is there a better way to handle this? If this is the right way to do it, is there a more prefered way if the number of columns you are trying to add is signifficantly greater? (i.e. 30 columns)
April 1, 2008 at 8:44 pm
A view is a Virtual Table, so what I am saying os that my view IS your second table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 8:45 pm
Corrected/clarified version of my view:
Create View yourTable2 AS
Select USER_ID
, VAL_1 + VAL_2 + VAL_3 + VAL_4 AS [Total]
From Table1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 11:09 pm
Ok, I believe I have the idea now but am still having problems puting my code together.
I have table A which contains user ID and assoiciated point values on each day (defined in the table). I can easily write a query which will add up the total score for each person based on their ID using the SUM function against a 'points' column.
I know that I must get that value from the statement above updated in to my 2nd table, ensuring that the UID from table A corasponds with the UID in table be.
Basically, in the first table there will be an entry for every day for every person defined and every day will have some score.
My 2nd table will have a single reference for each person with their cumulative score as summarized from the first table.
this has to be a Select SUM(... followed by an update using a trigger but I'm really stuggling on how to put this all together.
April 2, 2008 at 6:43 am
There are two things that need to be done here (and possibly 3). First, you will need to run a query to update totals in table 2 for values that are already in table 1. Second, you can use a trigger that will automatically fire anytime a value is updated in table 1 to recalculate totals in table 2. Also, if you plan on adding new user ID rows to table 1, you will probably want a trigger that will automatically insert the corresponding user ID into table 2 and calculate the initial total.
I am not entirely certain of your table structure as things seem to change 🙂 You may need the SUM() function if your data looks like this
table 1
USER_ID VAL1 VAL2
1 2 4
1 3 9
2 5 1
2 4 4
table 2
USER_ID total
1
2
But not if every user can only have one row in table 1. My code snippet assumes that each USER_ID can only appear once in table 1. If that is not the case, replace the additions with the SUM().
Please keep in mind that there are other ways to handle the automatic calculations, this is just one option. 🙂
Step 1. The query to update existing values:
UPDATE table2
SET t2.Total = t2.Total + t1.CARS + t1.BOATS + t1.PLANES
FROM table2 t2, table1 t1
WHERE t2.USER_ID = t1.USER_ID
GO
Step 2 is the update trigger and Step 3 is the optional insert trigger. I'm sorry this is all I have time for right now, but I will try to check back here later today.
Julia Morton
April 2, 2008 at 9:33 am
Or, if your table looks like your second example, and not your first, then just create the table with T_VEHICLES as a computed column. Again, no triggers, but everything is kept right up to date.
John
April 2, 2008 at 9:44 am
I'd go with a view as suggested earlier by rbarryyoung. Something like this will handle multiple rows for the User_ID. And, best of all, no triggers or additional work. The data in the view always reflects the data in the table. Simply select * from yourTable2 where USER_ID = xxx and you're done.
Create View yourTable2 AS
SELECT USER_ID, SUM(Val1 + Val2 + Val3 + Val4) AS [Total]
From Table1
GROUP BY USER_ID
ORDER BY USER_ID
-- You can't be late until you show up.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply