聚合列上的聚合函数

Question

我正在尝试此查询以获得如下表所示的结果。

Select Building, (Total_SRs/Select(SUM(Total_SRs)) From Serveu) As '%age'
From (Select Building, Count(Building) as Total_SRs From serveu Group by Building)

Building            Total_SRs   %age
Abbey Crescent 1    83          4.36%
Abbey Crescent 2    68          3.57%
Barton House 1      12          0.63%
Botton House 1      11          0.58%
Botton House 2       3          0.16%

Answer 1

我将使用ANSI标准窗口函数来编写以下代码：

Select Building, Count(*) as Total_SRs,
       count(*) * 1.0 / sum(count(*)) over () as ratio
From serveu
Group by Building;

* 1.0是因为一些数据库执行整数除法。

我更喜欢0和1之间的比率。如果你想将值格式化为字符串，这取决于你使用的数据库。