开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜