MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table NO2_avg ( Location VARCHAR(50),`DATE(``Date Time``)` DATE  , `COUNT(*)` int, `avg(NO2)`  float);




insert into NO2_avg values('Temple Way'           , '2017-09-07'        ,       24 , 27.305995000000006 );
insert into NO2_avg values('Parson Street School' , '2017-09-07'        ,       24 , 32.020833333333336 );
insert into NO2_avg values('Fishponds Road'       , '2017-09-07'        ,       24 ,             39.125 );
insert into NO2_avg values('Wells Road'           , '2017-09-07'        ,       24 , 41.354166666666664 );
insert into NO2_avg values('Brislington Depot'    , '2017-09-07'        ,       24 ,           23.09375 );
create table temperature_avg (`DATE(``Date Time``)` date, `COUNT(*)` int, `avg(Temperature)` float  );

insert into temperature_avg values('2017-09-07'        ,       48 , 4.791666666666667 );
insert into temperature_avg values('2022-03-04'        ,       48 , 5.333333333333333 );
insert into temperature_avg values('2022-03-03'        ,       48 ,            7.5625 );
insert into temperature_avg values('2022-03-02'        ,       44 , 7.090909090909091 );
insert into temperature_avg values('2022-03-01'        ,       48 , 5.791666666666667 );
SELECT `avg(NO2)`
FROM
     NO2_avg a1
INNER JOIN temperature_avg a2 ON a1.`DATE(``Date Time``)` = a2.`DATE(``Date Time``)`;


avg(NO2)


23.0938

41.3542

39.125

32.0208

27.306