sql-server - 根据总计计算百分比

Question

我目前有以下查询以按城市查看销售额 - 不包括伦敦。

SELECT
City, 
SUM(Sales) as Total
FROM Employees
WHERE City != 'London'
Group BY City

当前输出

City            Total    
Kirkland          3         
Redmond           4         
Seattle           9        
Tacoma            2

我需要在下方创建第三列“百分比”(%，无小数点)。你能帮我解决这个问题吗？非常感谢您!

期望的输出

City            Total    Percentage
Kirkland          3         17%
Redmond           4         22%
Seattle           9         50%
Tacoma            2         11%

Answer 1

尝试下面的查询。

SELECT
City, 
SUM(Sales) as Total
FROM Employees
WHERE City != 'London'
Group BY City

The result will be;
   City            Total
   Kirkland          3
   Redmond           4
   Seattle           9
   Tacoma            2

要计算百分比，

DECLARE @Total INT
SELECT @Total=SUM(Sales) FROM Employees WHERE City != 'London'

SELECT
 City, 
 SUM(Sales) as Total,
 CAST(SUM(Sales) * 100 / CAST(@Total AS DECIMAL(18,2)) AS DECIMAL (18,2))
FROM Employees
WHERE City != 'London'
Group BY City