SELECT treatment.service_id, service.service_charge, treatment.actual_charge FROM treatment JOIN service ON treatment.service_id = service.service_id WHERE treatment.service_id IN (SELECT service_id FROM service WHERE service_cat_id = 'SUR') AND service.service_charge <> treatment.actual_charge; Alter the above query to only list services where the difference between the service_charge and actual_charge is greater than the average difference between these two charges for services in the ‘SUR’ category of services. The result table should only list the service_id and service_description column from the service table. Hint: Compute the average difference between these two charges for ‘SUR’ services in a subquery
SELECT treatment.service_id, service.service_charge, treatment.actual_charge
FROM treatment
JOIN service ON treatment.service_id = service.service_id
WHERE treatment.service_id IN (SELECT service_id FROM service WHERE service_cat_id = 'SUR')
AND service.service_charge <> treatment.actual_charge;
Alter the above query to only list services where the difference between the service_charge and actual_charge is greater than the average difference between these two charges for services in the ‘SUR’ category of services. The result table should only list the service_id and service_description column from the service table. Hint: Compute the average difference between these two charges for ‘SUR’ services in a subquery
Step by step
Solved in 3 steps with 1 images
it returns with Msg 8124, Level 16, State 1, Line 8
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.
by Bartleby Expert
