SQLTeam.com | Weblogs | Forums

Summing not working correctly


Hi I want to sum up the market value for 2 columns in a table . i want to sum up where both [Security Type] = 'Ordinary Shares' and [Security Type]='N/A' and then update that figure back into the market value field where [Security Type] = 'Ordinary Shares'

my query is a s follows but it doesnt sum up the values correctly

SET m.MarketValue = f.MarketValue
FROM dbo.NTAISOIHoldingsTable m
SELECT [Legal Entity ID],Family,[Security Type], SUM([As at 30/06/2016]) MarketValue, SUM(Quantity) Quantity
FROM dbo.NTAISOIHoldingsTable
WHERE [Security Type] = 'Ordinary Shares' or [Security Type]='N/A'
GROUP BY [Legal Entity ID], Family,[Security Type]
) f ON m.[Security Type] = 'Ordinary Shares'

any one any ideas where i am going wrong


CAn you give some sample data, show what sum you want and what sum you get with your query?


76 CABB Ordinary Shares 31668247.960000000 31668247.950000000
76 CABB N/A 70881.110000000 0.000000000

so i want to roll up 31668247.960000000 and 70881.110000000 that should equal 31739129.07

i get 2711589.380000000


I think the problem may be here:

GROUP BY [Legal Entity ID], Family,[Security Type]

Your sum is three levels deep, but your update statement has no Where clause, so it will update all rows returned by the join.

Try adding a where clause to the update, to corelate the updates with the rows returned by the subquery