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)
Copy code

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 (namelymin andmax) + fixture_id.

You'd need something like this:

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

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);
Copy code

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).