PDA

View Full Version : Why is my SQL query not giving the correct results



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

netsrak
07-20-2009, 04:47 AM
Please contact support@holdemmanager.net

Rvg72
07-21-2009, 01:31 PM
Hi, ROI should be something like this:

((SUM(tourneyplayerdata.winnings)/1.0/SUM(tourneydata.buyin + tourneydata.rake))-1)*100

Roy

dw33p
07-21-2009, 01:37 PM
Hi, ROI should be something like this:

((SUM(tourneyplayerdata.winnings)/1.0/SUM(tourneydata.buyin + tourneydata.rake))-1)*100

Roy

If i am not mistaken this is the total ROI. The total ROI is something different as the average ROI. However, I cant test it out right now, ill test tomorrow. (e.g. the total roi will differ from the average roi if someone played 100 10& sit&go with a roi of 5% and 1 10K$ sit&go with a roi of 100%).