The SUM() is counting all the values, instead of the ones that meet the condition

I have following query to write:

Knowing that a team will earn at least the market value (WartoscRynkowa) of a given player from the sale of a player and knowing the amount of his transfer (KwotaTransferu), calculate how much the team will earn or lose by selling players with the highest chance of transfer (> = 2) (SzansaNaTransfer), provide data for each a player who meets the above condition (player data, amount) and provide the value for all of them together;

Here are the tables:

I did a query as the following one:

SELECT *, Imie+' '+Nazwisko AS ImieNazwisko,
                                            (SELECT SUM(WartoscRynkowa) -SUM(KwotaTransferu)
                                            FROM Beata.dane
                                            ) AS Result
FROM Beata.dane AS D
JOIN Beata.team AS T
ON D.NumerKoszulki = T.NumerKoszulki
WHERE SzansaNaTransfer >= 1

The Result 130 is wrong, because it counts also the players. It should be 33, because the sum of WartoscRynkowa(market value) is 215 minus KwotaTransferu(amount of transer) – 182 = 33

Why the query counts also the amount of the players that don’t meet the condition “>=1”

EDIT: I’ve updated the query and getting the following result. How to summarize the total value in the other column (I mean summarize all the Results)?

SELECT T.Imie, T.Nazwisko,
                                            (SELECT SUM(WartoscRynkowa) - SUM(KwotaTransferu)
                                            FROM Beata.dane NAD
                                            WHERE NAD.WartoscRynkowa = D.WartoscRynkowa
                                            ) AS Result
FROM Beata.dane AS D
JOIN Beata.team AS T
ON D.NumerKoszulki = T.NumerKoszulki
WHERE SzansaNaTransfer >= 2

Answer

I find the question rather hard to follow, not understanding what the column names are. I don’t think you need aggregation to solve this, but you need a way to calculate the total — and window functions are one method:

SELECT (t.Imie + ' ' + t.Nazwisko) AS ImieNazwisko,
       (WartoscRynkowa - KwotaTransferu) as Result,
       SUM(WartoscRynkowa - KwotaTransferu) OVER () as overall_total
FROM Beata.dane D JOIN 
     Beata.team AS T
     ON D.NumerKoszulki = T.NumerKoszulki
WHERE SzansaNaTransfer >= 2;