sql ,compare aggregate avg function in a sub query

Question

My problem is as follows, ServiceTypeWithAvgPriceBelowAvgServicePrice is a view that provides details of service types with an average price that is below the average service price. Using the sample data, the query below should produce the following result:

ServiceType                | ServiceTypeAvgPrice | ServiceAvgPrice
3rd drafting               |   280               |   522.7083
4th drafting               |   320               |   522.7083
client consultation        | 172.5               |   522.7083
design selection           |   300               |   522.7083
initial consultation       |     0               |   522.7083

sample data

Description             Actual Price
initial consultation    $0
initial consultation    $0
design selection        $300
drafting                $1200
council approval        $600
client consultation     $360
develop design          $2070
client consultation     $180
client consultation     $180
client consultation     $135
initial consultation    $0
design selection        $300
drafting                $1200
council approval        $600
client consultation     $90
1st drafting            $1920
2nd drafting            $920
3rd drafting            $280
council approval        NULL
initial consultation    $0
initial consultation    $0
design selection        $300
drafting                $1500
council approval        $600
client consultation     $90
4th drafting            $320
initial consultation    $0

I was trying to retrieve the 5 average values expected below the total average of actual price, but it was very difficult to me to compare each of unique description's average with total price. i was trying a sub query as follows,

select avg(actualPrice) as serviceytpeAvg 
from service 
having avg(actualprice) > (
                    select avg(actualprice) 
                    from service 
                    group by descrption)

But it was rejected because it cannot return more than one value for sub query. What is the solution?

Answer 1

You can use OUTER APPLY

SELECT  s.[Description] as ServiceType, 
        AVG(s.[Actual Price]) as ServiceTypeAvgPrice ,
        AvgPrice as ServiceAvgPrice
FROM [Service] s
OUTER APPLY (
    SELECT  AVG(s.[Actual Price]) as AvgPrice
    FROM [Service] s) p
GROUP BY s.[Description], AvgPrice
HAVING AVG(s.[Actual Price]) < AvgPrice

Or simple:

SELECT  s.[Description] as ServiceType, 
        AVG(s.[Actual Price]) as ServiceTypeAvgPrice ,
        (SELECT AVG([Actual Price]) FROM [Service]) as ServiceAvgPrice
FROM [Service] s
GROUP BY s.[Description]
HAVING AVG(s.[Actual Price]) < (SELECT AVG([Actual Price]) FROM [Service])

Output:

ServiceType             ServiceTypeAvgPrice ServiceAvgPrice
3rd drafting            280,00              505,5769
4th drafting            320,00              505,5769
client consultation     172,50              505,5769
design selection        300,00              505,5769
initial consultation    0,00                505,5769