sql - Averaging the result of a subquery in mySQL -

i'm building query searches through medicare database listing how doctors charge various procedures.

ideally, query would:

• return every record, meaning every procedure every doctor. (i'll add filtering where clauses later)
• return average amount doctors charge each procedure
• return percentage difference between average cost , each individual doctor charges
• return average of percentage differences each doctor, generating meta cost-differential score.

with query below, i've been able achieve last goal.

select medicare.*,         peeraverage.average charge_average,         ( medicare.average_submitted_chrg_amt - peeraverage.average ) /         peeraverage.average * 100 difference_from_average,        avg( ( medicare.average_submitted_chrg_amt - peeraverage.average ) /         peeraverage.average * 100 ) total_difference_from_average   medicare         join (select avg(average_submitted_chrg_amt) average,                      procedure_code                 medicare               group  procedure_code) peeraverage           on medicare.procedure_code = peeraverage.procedure_code  order  procedure_code asc,            difference_from_average desc  

when add final select condition (avg( ( medicare.average_submitted_chrg_amt - peeraverage.average ) / peeraverage.average * 100 ) total_difference_from_average), query returns 1 record.

delete condition , query returns correct number of records. doing wrong?

aggregation functions move aggregation level up. until have specified grouping conditions average function, return 1 row aggregated on values, returned expression