dimanche 2 juillet 2017

MYSQL - SUM values of last 10 days

I have this table in mysql:

|   player1   |  player 2 |    date    |   fs_1   |   fs_2   |
    Jack         Tom       2015-03-02       10         2
    Mark        Riddley    2015-05-02       3          1 
     ...

I need to know how many aces (fs_1) player 1 have done BEFORE the match reported in date_g (10 days before for example).

This is what i tried without success:

OPTION 1

    SELECT 
    players_atp.name_p AS 'PLAYER 1',
    P.name_p AS 'PLAYER 2',
    DATE(date_g) AS 'DATE',
    result_g AS 'RESULT',
    FS_1,
    FS_2,
    SUM(IF(date_sub(date_g, interval 1 day)< date_g, FS_1, 0)) AS 'last 20 days'
FROM
    stat_atp stat_atp
        JOIN
    backup3.players_atp ON ID1 = id_P
        JOIN
    backup3.players_atp P ON P.id_p = id2
        JOIN
    backup3.games_atp ON id1_g = id1 AND id2_g = id2
        AND id_t_g = id_t
        AND id_r_g = id_r
WHERE
    date_g > '2015-01-01'
GROUP BY ID1;

OPTION 2

SELECT 
    players_atp.name_p AS 'PLAYER 1',
    P.name_p AS 'PLAYER 2',
    DATE(date_g) AS 'DATE',
    result_g AS 'RESULT',
    FS_1,
    FS_2,
    SUM(CASE WHEN date_g between date_g and date_sub(date_g, interval 10 day) then fs_1 else 0 end) AS 'last 20 days'
FROM
    stat_atp stat_atp
        JOIN
    backup3.players_atp ON ID1 = id_P
        JOIN
    backup3.players_atp P ON P.id_p = id2
        JOIN
    backup3.games_atp ON id1_g = id1 AND id2_g = id2
        AND id_t_g = id_t
        AND id_r_g = id_r
WHERE
    date_g > '2015-01-01'
GROUP BY ID1;

Thanx in advance.

Aucun commentaire:

Enregistrer un commentaire