PDA

View Full Version : Results-orientated query



Sn1per
04-25-2011, 12:10 PM
How many times have I played villain and how many times has he beaten me?

(I play HeadsUP SnGs).

a.k.a.Also
04-25-2011, 02:47 PM
Assuming you don't have any mined hands on villain, just select them as the player in Tourney results > Data view

11031

Sn1per
04-25-2011, 04:05 PM
Thanks, this will suffice. Now this is probably the wrong place, but is it possible to run a query and pull out the names of those who are consistently beating me?

edit: never mind, found this (hope it helps someone else as well)

SELECT
COUNT(tpd.finishposition) matches,
p2.playername,
SUM((CASE WHEN(tpd.finishposition = 1) THEN 1 ELSE 0 END)) AS win,
SUM((CASE WHEN(tpd.finishposition = 2) THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN(tpd.finishposition = 3) THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN(tpd.finishposition = 4) THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN(tpd.finishposition = 1) THEN 1 ELSE 0 END))*100 / COUNT(tpd.finishposition) percent
FROM
tourneyplayerdata tpd
join players p ON (tpd.player_id = p.player_id AND p.playername = 'hero')
join pokersites ps ON (tpd.site_id = ps.site_id AND ps.abbreviation = 'PS')
join tourneydata td ON (tpd.tourneynumber = td.tourneynumber AND tpd.site_id = td.site_id)
join tourneyplayerdata tpd2 ON (tpd.player_id != tpd2.player_id AND tpd.tourneynumber = tpd2.tourneynumber AND tpd.site_id = tpd2.site_id)
join players p2 ON (tpd2.player_id = p2.player_id AND tpd2.site_id = p2.site_id)
WHERE
tpd.tourney_ended
AND td.tourneysize = 4
AND td.pokergame = 1
GROUP BY p2.playername
ORDER BY matches DESC
limit 100