0

I need to calculate a percentage based on two rows next to each other. The Select statement finds them and calculates correctly but the Update error says:

Incorrect syntax near the keyword 'Group'

Code:

Update L 
Set [TCKR%] = (L.Stock_Close - E.Stock_Close) / L.Stock_Close 
From HistData as L
Inner Join HistData as E on L.RecordID = E.RecordID + 1
Where L.RecordID = L.RecordID
Group by L.RecordID, L.Stock_Close, E.Stock_Close

Order by does not work either

Steven
  • 896
  • 2
  • 16
  • 29
R2 Builder
  • 99
  • 3
  • 12
  • 2
    You can't use an aggregate in an UPDATE query. Check this answer: http://stackoverflow.com/a/2853556/5948715 – Balde Apr 01 '16 at 17:47

1 Answers1

0

I don't think there is a reason to perform a GROUP BY in this case:

UPDATE L
SET [TCKR%] = (L.Stock_Close -  E.Stock_Close)/L.Stock_Close 
FROM HistData as L
JOIN HistData AS E on L.RecordID = E.RecordID + 1

Note: If Stock_Close is of type int then you have to multiply either the nominator or the denominator by 1.0 so as to avoid integer division.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • the records are not in order unless I order them. This works correctly with group by without it; it updates the wrong rows, Also [TCKR%] is decimal(9,6): the select works as I said. So it needs to Order the Rows by RecordID somehow. – R2 Builder Apr 01 '16 at 18:09
  • Actually your code makes it off my one row its updating the row below RecordID TICKR% 1 NULL 2 0.0005 3 -0.0003 – R2 Builder Apr 01 '16 at 18:11
  • Or how can I reorder the Table so RecordID is Ascending by default? – R2 Builder Apr 01 '16 at 18:16
  • @R2Builder Can you provide some sample data along with expected result? – Giorgos Betsos Apr 01 '16 at 18:19
  • @R2Builder Do you get the correct result if you use on `L.RecordID = E.RecordID - 1` instead of on `L.RecordID = E.RecordID + 1` ? – Giorgos Betsos Apr 01 '16 at 18:26
  • Well Record ID 1 is the first record so if you subtract 1 you get a Record ID of 0 and it does not work and then the equation is backwards for the rest. Sample Data, not easily. I have to put the data out on the WEB somewhere which is not do-able for this client. – R2 Builder Apr 01 '16 at 18:38