时间戳

首页 > 解决方案 > 基于总和计算 Percetange

sql-server - 基于总和计算 Percetange

问题描述

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

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%

标签： sql-server

解决方案

试试下面的查询。

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

推荐阅读