dw33p
07-20-2009, 04:14 AM
Hello,
Im writing a few querys against the HM and PT3 databases to retreive statistics about players.
The following query should give me the statistics that are also used by sharkscope. I have inserted 98 tournament results and every stat is correct exept the Avarage ROI stat. It returns -51,XX which is totally wrong, its ~-6%. If I execute the query on the PT3 database (obviously altered with the correct table/column names) it does work correctly.
What am I doing wrong here?
SELECT
COUNT(tourneydata.*) AS gamesPlayed,
SUM(tourneydata.buyin + tourneydata.rake)/100 as totalSpent,
AVG(tourneydata.buyin)/100 AS avgStake,
SUM(tourneyplayerdata.winnings)/100 AS totalWon,
AVG(tourneyplayerdata.winnings - (tourneydata.buyin + tourneydata.rake))/100 AS avgWon,
AVG((tourneyplayerdata.winnings - (tourneydata.buyin + tourneydata.rake)) / (tourneydata.buyin + tourneydata.rake) * 100) AS avgRoi
FROM tourneyplayerdata
INNER JOIN tourneydata ON tourneydata.tourneynumber=tourneyplayerdata.tourne ynumber
WHERE tourneyplayerdata.player_id IN (
SELECT player_id FROM players WHERE playername='<nick>' AND site_id IN (
SELECT site_id FROM pokersites WHERE sitename ='Full Tilt'
)
)
Edit: The results I get from both HM and PT3:
Result HM:
gamesplayed: 98
totalspent: 29220
avgstake: 286.2244897959183700
totalwon: 27060
avgwon: -22.0408163265306122
avgroi: -51.0204081632653061
Result PT3:
gamesplayed: 98
totalspent: 29220.00
avgstake: 286.2244897959183673
totalwon: 27060.00
avgwon: -22.0408163265306122
avgroi: -6.1457992808107224582
Im writing a few querys against the HM and PT3 databases to retreive statistics about players.
The following query should give me the statistics that are also used by sharkscope. I have inserted 98 tournament results and every stat is correct exept the Avarage ROI stat. It returns -51,XX which is totally wrong, its ~-6%. If I execute the query on the PT3 database (obviously altered with the correct table/column names) it does work correctly.
What am I doing wrong here?
SELECT
COUNT(tourneydata.*) AS gamesPlayed,
SUM(tourneydata.buyin + tourneydata.rake)/100 as totalSpent,
AVG(tourneydata.buyin)/100 AS avgStake,
SUM(tourneyplayerdata.winnings)/100 AS totalWon,
AVG(tourneyplayerdata.winnings - (tourneydata.buyin + tourneydata.rake))/100 AS avgWon,
AVG((tourneyplayerdata.winnings - (tourneydata.buyin + tourneydata.rake)) / (tourneydata.buyin + tourneydata.rake) * 100) AS avgRoi
FROM tourneyplayerdata
INNER JOIN tourneydata ON tourneydata.tourneynumber=tourneyplayerdata.tourne ynumber
WHERE tourneyplayerdata.player_id IN (
SELECT player_id FROM players WHERE playername='<nick>' AND site_id IN (
SELECT site_id FROM pokersites WHERE sitename ='Full Tilt'
)
)
Edit: The results I get from both HM and PT3:
Result HM:
gamesplayed: 98
totalspent: 29220
avgstake: 286.2244897959183700
totalwon: 27060
avgwon: -22.0408163265306122
avgroi: -51.0204081632653061
Result PT3:
gamesplayed: 98
totalspent: 29220.00
avgstake: 286.2244897959183673
totalwon: 27060.00
avgwon: -22.0408163265306122
avgroi: -6.1457992808107224582