SQL query avg function not working
At the outermost level, you are returning the average values along with pr.pid. If you want averages per pid, then you need to group your rows by pid.

You are doing that in the nested queries but not in the main one. So, after on tr.pid=pr.pid just add group by pr.pid.

But if you want the average value across the entire row set, remove pr.pid from the (main) select list.