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;
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
Advertisement
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;