我的问题如下,ServiceTypeWithAvgPriceBelowAvgServicePrice
是一种视图,它提供了平均价格低于平均服务价格的服务类型的详细信息。使用示例数据,以下查询应产生以下结果:
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
示例数据
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
我试图检索预期的5个平均值低于实际价格的总平均值,但是我很难将每个独特描述的平均价格与总价格进行比较。我正在尝试以下内容的子查询,
select avg(actualPrice) as serviceytpeAvg
from service
having avg(actualprice) > (
select avg(actualprice)
from service
group by descrption)
但是它被拒绝了,因为它不能返回子查询的一个值以上。什么是解决方案?
您可以使用外部应用
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
或简单:
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])
输出:
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