The head of hospital auditing wants to know which patients were charged the most for a treatment. The result table should list three columns from the treatment table appropriately labeled: pat_id, actual_charge, and service _id .Only list rows where the patient was charged the most for a treatment. This may result in some rows where patients were charged identical amounts to other patients. Sort the rows by service_id. Use a correlated subquery. Format the output appropriately.

I executed :

select pat_id, actual_charge from treatment where actual_charge > (select avg(service_charge - actual_charge) from service where service_cat_id = 'SUR');

and got this error message:

Msg 8124, Level 16, State 1, Line 12 Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.