SQL query to get the bookmakers with the highest odds
I try to solve an sql problem but without success and I thought that you could give me a good answer.
My table looks like this:
league_id|style|bookmaker|hometeam|awayteam|odd1|oddX|odd2| ----------------------------------------------------------- premier |1X2 |bet365 开发者_开发问答|arsenal |chelsea |2.50|3.20|2.30| ----------------------------------------------------------- premier |1X2 |unibet |arsenal |chelsea |2.40|3.00|2.00| ----------------------------------------------------------- premier |1X2 |ladbrokes|arsenal |chelsea |2.60|3.20|2.10| ----------------------------------------------------------- premier |1X2 |bet24 |arsenal |chelsea |2.30|3.40|2.10| -----------------------------------------------------------
odd1 = home team to win, odd2 = away team to win, oddX = draw
DESIRABLE OUTPUT:
Premier League Odds: team |AVERAGE|BEST|BEST ODDS BOOKMAKER| ----------------------------------------- arsenal | 2.45 |2.60| ladbrokes | ----------------------------------------- chelsea | 3.20 |3.40| bet24 | ----------------------------------------- Draw | 2.12 |2.30| bet365 | -----------------------------------------
you can see a live example here: http://www.wsn.com/football/football-odds/
Thanks a lot in advance.
Depending on your dialect of SQL, using a subquery along these lines might be what you're looking for, in case I understand correctly what the numbers mean:
SELECT
tt.hometeam as team,
avg(tt.odd1) as average,
max(tt.odd1) as best,
tt.bookmaker as best_odds_bookmaker
FROM
2_team_table tt
INNER JOIN
(
SELECT
bookmaker as maxbookie
FROM
2_team_table
ORDER BY
odd1 desc
LIMIT 1
) as a
ON tt.bookmaker = a.maxbookie
UNION ALL
..... ( same thing with awayteam and odd2 )
UNION ALL
..... ( same thing setting the first col to "Draw" and using oddX )
BUT REALLY you're much better off doing something like this in some scripting language, and it'll ONLY work like this if your table only contains two teams, one event, which is very unrealistic.
精彩评论