php - Calculate difference between two avg based on same value from different columns

Solution:

You can do this by joining the table on itself:

select
    t_max.*,
    (t_max.H_odds - t_min.H_odds) as dif_h,
    (t_max.D_odds - t_min.D_odds) as dif_d,
    (t_max.A_odds - t_min.A_odds) as dif_a
from
(
    select
        fixture_id,
        min(updated) min_updated,
        max(updated) max_updated
    from
        test
    group by
        fixture_id
) as t1
join test as t_min on (t_min.fixture_id = t1.fixture_id and t_min.updated = t1.min_updated)
join test as t_max on (t_max.fixture_id = t1.fixture_id and t_max.updated = t1.max_updated)

Working example.

In MySQL >= 8 there may be a solution using window-functions, but im not too sure about that since the fields you need can not be fetched by an aggregate function itself, they are just identified by an aggregate (namely min and max) + fixture_id.

You'd need something like this:

SELECT
    MIN(updated) over (partition by fixture_id)
...

This get max updated for all grouped 'fixture_id' (but you dont want 'min(updated)', you want 'H_odds' etc. of the row having 'min(updated) AND fixture_id = "fixture_id of max(updated) with same fixture_id') - which, please correct me if im wrong, does not exist. But since you dont use MySql >= 8 anyways this is not an option.

And please get unique ids :)

Important

You need to index your table:

// recommended if you plan to narrow down your results by this fields - which i assume
create index t_f on test (fixture_id);
create index t_u on test (updated);

// MUST HAVE
create unique index t_fu on test (fixture_id, updated);

If you dont do so, there will be temptables on your filesystem - (very very slow)

Working example.

Update: A rather unpleasant MySQL >= 8 window-function version (using filesort - DO NOT USE).