Multiple table SQL problem

[HarryCohen]

The following is part of a stored procedure that is meant to return outstanding money for a specific bank. Normally this works fine. However when there is a trade with multiple legs of which the intUnits value is identical then the SUM(ISNULL(Payments.smnyValue,0)) returns a value that is multiplied by however many identical values there were, giving me incorrect results. Therefore my resultset only contains one row where i'd expect multiple rows. Any ideas how to solve this? Thanks in advance.


SELECT Trades.intTradeID, TradesLegs.intUnits,
CASE WHEN (Trades.intBuyerBankID = @BankID) THEN
Trades.mnyBuyerBrokerage - SUM(ISNULL(Payments.smnyValue,0))
ELSE
Trades.mnySellerBrokerage - SUM(ISNULL(Payments.smnyValue,0))
END
FROM Trades
LEFT JOIN TradesLegs ON TradesLegs.intTradeID = Trades.intTradeID
LEFT JOIN Payments ON ISNUMERIC(Payments.chrTradeID) = 1
AND CAST(Payments.chrTradeID as int) = Trades.intTradeID AND Payments.intBankID = @BankID
GROUP BY Trades.intTradeID, TradesLegs.intUnits, Trades.intBuyerBankID,
Trades.mnyBuyerBrokerage, Trades.mnySellerBrokerage

 

[HarryCohen]

Of course posting here was all I needed to work out the problem.

I changed it from SUM(ISNULL(Payments.smnyValue,0)) to SUM(DISTINCT ISNULL(Payments.smnyValue,0)) and it does what it should.