the best way is to create a view, which would calculate that information on demand.
Best practice is to never add a column containing agregates in a table row, since it woudl require an update every time data changes.
you could achieve the same thing in line with a user defined function and a calculated column that uses that function, but the view is my recommendation.
SELECT 'TRADE1',500 UNION ALL
SELECT 'TRADE1',1000 UNION ALL
SELECT 'TRADE2',2000 UNION ALL
INNER JOIN(SELECT [TradeID],SUM([nominal]) As position FROM MyCTE GROUP BY [TradeID]) Sumz
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!