Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
seeker62
Starting Member
40 Posts |
Posted - 2012-11-12 : 17:54:45
|
I have an insert statement with about 40 columns in the select statement. I need to have one of the columns be filled with calculated data. I thought i would insert the data and then do an update i.e.update Tableset column = sum(columna - columnb)but that can not be done because aggregates are not allowed in set statement. Can I get around this without putting my 40 columns in the insert statement into the group by segment.Thanks for the input |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-12 : 18:11:01
|
Not really sure how to advise you with the information you provided. However, I could guess that you need to use a deriuved table (or inline view) or an outter apply in order to do the update. Here is an example of one way you might do that:UPDATE T SET Column = D.SumColumnFROM TableName AS TINNER JOIN ( SELECT ID, SUM(columna - columnb) AS SumColumn FROM TableName GROUP BY Column_list ) AS D ON T.ID = D.ID If that doesn't help, here are some links that can help you provide more information:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2012-11-13 : 09:52:13
|
Thank you that did the trick. New problem though the sum is not giving the correct output.set columnc = sum(columnb - columna)ifcolumnb = 105800 and columna = 40000 then columnc should be 65800 but it is 180440 what aggregate calculation function should i be useingGot it fixed just used simple columnb - columna in insert statement |
|
|
|
|
|