CODEWITHSUNDEEP
sqlsql-server
Beti
Beti

Reputation: 175

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: enter image description here

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

enter image description here

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

enter image description here

Upvotes: 0

Views: 204

Answers (2)

XavierR
XavierR

Reputation: 21

SELECT DISTINCT a.team,sum(a.MARKET_VALUE) OVER (PARTITION BY TEAM) as totalMarketValue, 
sum(a.TRANSFER_AMOUNT) OVER (PARTITION BY TEAM) As TotalTransferAmount,
(sum(a.TRANSFER_AMOUNT) OVER (PARTITION BY TEAM) - sum(a.MARKET_VALUE) OVER (PARTITION BY TEAM)) AS TEAM_EARNINGS
FROM A
JOIN B
ON a.ID = b.ID
where b.TRANSFER_CHANCE >= 2

If i understand correctly you want the earnings for each team. Sum market value and Transfer amount for each team and subtract the 2.

team totalMarketValue TotalTransferAmount TEAM_EARNINGS
Portugal 40 50 10
Ukraina 3 9 6
wychowanek 80 0 -80

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270351

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;

Upvotes: 1

Related Questions