SQL query to get the bookmakers with the highest odds.

Question

Hello,

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.

Answer 1

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.

Answer 2

This will bring you back the data you need. It will still need an Unpivot in your application

SELECT 
o1.league_id, o1.style, o1.hometeam, o1.awayteam,
MAX(o1.odd1) AS BestHome, 
MAX(o1.oddX) AS BestDraw, 
MAX(o1.odd2) AS BestAway,
(SELECT group_concat(bookmaker) FROM Odds WHERE MAX(o1.odd1) = odd1 AND o1.league_id = league_id and o1.style = style and o1.hometeam = hometeam and o1.awayteam = awayteam) AS BestHomeBM,
(SELECT group_concat(bookmaker) FROM Odds WHERE MAX(o1.oddX) = oddX AND o1.league_id = league_id and o1.style = style and o1.hometeam = hometeam and o1.awayteam = awayteam) AS BestDrawBM,
(SELECT group_concat(bookmaker) FROM Odds WHERE MAX(o1.odd2) = odd2 AND o1.league_id = league_id and o1.style = style and o1.hometeam = hometeam and o1.awayteam = awayteam) AS BestAwayBM,
AVG(o1.odd1) AS AvgHome, 
AVG(o1.oddX) AS AvgDraw, 
AVG(o1.odd2) AS AvgAway
FROM Odds o1
GROUP BY o1.league_id, o1.style, o1.hometeam, o1.awayteam