mysql - How to get Max average value and its Id grouped by month -

i want max average value , id grouped month,

select fk_transporter,month, max(avg_rate) btpercent (     select kpi.fk_transporter ,kpi.`month`, avg(kpi.ontimedelivery) avg_rate     primarykpi kpi,transporter t         kpi.fk_transporter=t.id , year(kpi.`month`)=2016     group fk_transporter, month(kpi.month)) tp group tp.`month` 

this query gave me right average id incorrect. couldn't able find mistake .here output of query

fk_transporter       month             maxavg 1                    2016-01-01        2.270109534263611 1                    2016-02-01        0.8539329767227173 1                    2016-03-01        0.6764709949493408 

fk_transporter 1 in records whch wrong . how can fix this. thanks.

here fiddle link http://sqlfiddle.com/#!9/02d7c/1/0

the problem group per month in outer query, mysql pretty picks 1st fk_transporter value encounters while scanning results of inner subquery. in outer query need max averages per month , join on inner subselect results:

select t2.fk_transporter, t.month, t.btpercent (select `month`, max(avg_rate) btpercent (     select kpi.fk_transporter ,kpi.`month`, avg(kpi.ontimedelivery) avg_rate     primarykpi kpi,transporter t         kpi.fk_transporter=t.id , year(kpi.`month`)=2016     group fk_transporter, month(kpi.month)) tp group tp.`month`) t inner join  (     select kpi.fk_transporter ,kpi.`month`, avg(kpi.ontimedelivery) avg_rate     primarykpi kpi,transporter t         kpi.fk_transporter=t.id , year(kpi.`month`)=2016     group fk_transporter, month(kpi.month)) t2 on t2.month=t.month , t2.avg_rate=t.btpercent 

sqlfiddle