I'm trying to create a query, that groups by player name, the count of tourn_id, victories (count of position=1), cash_prizes (count of winnings>0), sum of winnings, sum of entry_fee, net winnings (sum of winnings - sum of entry_fee)
With the help of truittb (the one who signs Fiat Lux), I get this, but I keep getting error msg "type mismatch in expression"
SELECT P.player_name, Count(T.tourn_id) AS CountOftourn_id, Sum(S.winnings) AS SumOfwinnings, Sum(T.entry_fee) AS SumOfentry_fee, (SELECT Count(position) FROM status WHERE position =1
AND status.player_name= P.Player_Name) AS victories, (SELECT Count(winnings) FROM status WHERE winnings>0
AND status.player_name= P.Player_Name) AS Cash_prizes
FROM tourn AS T INNER JOIN (players AS P INNER JOIN status AS S ON P.player_name=S.player_name) ON T.tourn_id=S.tourn_id
GROUP BY P.player_name;