在 SQL Server 中使用 GROUP BY 子句时，根据总行数获取行的百分比

Question

使用此 SQL 查询：

Select CityName, count(UserID) as UserCount 
from tblUsers 
group by CityName

我得到这些结果：

CityName    UserCount
---------------------
City 1      10
City 2      15

预期产出：

CityName    UserCount  Perc
---------------------------
City 1      10         40
City 2      15         60

根据上面的数据集，我想根据 1 列中值的总和获取行的 % 分布。请指教。

Answer 1

尝试使用子查询来选择总数，如下所示：

Select CityName,
       count(userID) as userCount,
       count(UserID)/(select count(*) from tblUsers)*100 as Perc
from tblUsers
group by CityName

您也可以像这样尝试加入：

SELECT t.CityName,
       t.userCount,
       t.userCount/s.totalCount*100 as Perc
FROM (Select CityName, count(UserID) as UserCount 
      from tblUsers 
      group by CityName) t
INNER JOIN (SELECT count(*) as totalCount from tblUsers) s
ON(1=1)

Answer 2

最简单的方法是使用窗口函数：

Select CityName, count(UserID) as UserCount ,
       count(UserId) * 100.0 / sum(count(UserId)) over () as Perc
from tblUsers 
group by CityName;

子查询不是必需的。

Answer 3

不需要

子查询/连接，只需使用组和：

Select CityName,
       count(userID) as userCount,
       100 * count(UserID)/sum(count(UserID)) over () as Perc
from tblUsers
group by CityName

sum(count(UserID)) over ()返回所有计数的总和。

而不是count(UserID)您可能可以使用COUNT(*)