Click here to Skip to main content
15,439,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want the max Average value and its id grouped by month,

SQL
Select FK_Transporter,Month, MAX(avg_rate) as BTPercent
FROM (
    SELECT kpi.FK_Transporter ,kpi.`Month`, AVG(kpi.OnTimeDelivery) as avg_rate
    FROM primarykpi as kpi,transporter as t
		WHERE kpi.FK_Transporter=t.Id and YEAR(kpi.`Month`)=2016
    GROUP BY FK_Transporter, MONTH(kpi.Month)) tp
GROUP BY tp.`Month`


This query gave me right average but id is incorrect. Couldn't able to fing the mistake .Here is output of this 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 is 1 in all records whch is wrong . How can i fix this. Thanks.
Posted
Updated 24-Jan-16 10:45am
v2
Comments
_Asif_ 25-Jan-16 8:17am     CRLF
What is the result of this query "SELECT kpi.FK_Transporter ,kpi.`Month`, AVG(kpi.OnTimeDelivery) as avg_rate FROM primarykpi as kpi,transporter as t WHERE kpi.FK_Transporter=t.Id and YEAR(kpi.`Month`)=2016 GROUP BY FK_Transporter, MONTH(kpi.Month)"

1 solution

SELECT * FROM data
WHERE temp_hi = (SELECT MAX(temp_hi) FROM data)
 
Share this answer
 
Comments
virel rakholiya 5-Apr-16 4:51am     CRLF
Use Below Statement... SELECT * FROM data WHERE temp_hi = (SELECT MAX(temp_hi) FROM data)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900