Summing columns

Transact-SQL
1

hi i have the following query

UPDATE m
SET m.MarketValue = f.MarketValue
FROM dbo.NTAISOIHoldingsTable m
INNER JOIN
(
  SELECT [Legal Entity ID],Family,[Investment Cost], SUM([As at 30/06/2016]) MarketValue
  FROM dbo.NTAISOIHoldingsTable
WHERE [Investment Cost]='0100' AND [Investment Cost]='0400'AND [Investment Cost]='0900'and Family=Family
  GROUP BY [Legal Entity ID], Family,[Investment Cost]
) f ON m.[Legal Entity ID] = f.[Legal Entity ID] AND m.Family  = f.Family;

I want to sum the as at field based on the investcome equa to 0100 and 0400 and 0900.

when i use and and in the query noting happens when i use or then it doesnt sum up correct e.g et a value for 0100 and 0400 for the same family when i want just one summed value. any ideas why that is happening

2

You're probably looking for something like this:

UPDATE m
   SET m.MarketValue = f.MarketValue
  FROM dbo.NTAISOIHoldingsTable m
       INNER JOIN (SELECT [Legal Entity ID]
                         ,Family
                         ,SUM([As at 30/06/2016]) MarketValue
                     FROM dbo.NTAISOIHoldingsTable
                    WHERE [Investment Cost] in ('0100','0400','0900')
                    GROUP BY [Legal Entity ID]
                            ,Family
                  ) f
               ON m.[Legal Entity ID] = f.[Legal Entity ID]
              AND m.Family  = f.Family
;
3

thanks will try that