我今天的问题是这样的:
有多少客户高于平均水平?我以这种方式回答,但这是错误的
select avg(height), count(*) as numofppl
from client
group by height
having height > avg(height)
某人可以提供其他解决方案(谁工作:))
select count(*) as numofppl
from client
where height > ( select avg(height) from client )
尝试此查询
count(*)
计数所有height > avg height
select count(*) as numofppl from client where height > ( select avg(height) from client )
按高度组。因此,您可以获得一排高度为100,一个高度200等。现在,您询问每组的避免速度是什么,但是所有组高度100的记录均具有高度= 100,因此Avarage当然是100。having height > avg(height)
仅在您的查询中仅是having height > height
,因为group by height
。
您要与之比较的不是每个高度的避免,而是总和:无GROUP BY
因此:
select count(*)
from client
where height > (select avg(height) from client);
如果您想显示越来越高的高度,请加入:
select x.avg_height, count(*)
from client c
cross join (select avg(height) as avg_height from client) x
where c.height > x.avg_height;
(根据DBMS,您可能必须在x.avg_height
上使用聚合功能,例如select max(x.avg_height)
。)