Home Forums Data Warehousing Integration Services how to add Aggregated Transform as a new column RE: how to add Aggregated Transform as a new column

  • April 28, 2014 at 7:22 am

    #1709156

    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.

    ;WITH MyCTE([TradeID],[nominal])

    AS

    (

    SELECT 'TRADE1',500 UNION ALL

    SELECT 'TRADE1',1000 UNION ALL

    SELECT 'TRADE2',2000 UNION ALL

    SELECT 'TRADE2',2000

    )

    SELECT

    MyCTE.[TradeID],

    MyCTE.[nominal],

    Sumz.[position]

    FROM MyCTE

    INNER JOIN(SELECT [TradeID],SUM([nominal]) As position FROM MyCTE GROUP BY [TradeID]) Sumz

    ON MyCTE.[TradeID]=Sumz.[TradeID]

    Lowell

    --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!