使用分组病例语句计算总数的百分比

Question

所以我有一个CASE语句，我已经分组了。但是，我还试图计算每个分组CASE结果的总数百分比。当我运行下面进行的命令，它给

Region   Number Percentage 
West Coast  11675 0 
Not West Coast 104620 0 

我不明白为什么“比例”来作为“0”。

下面是代码，标有'问题行'。

With [Summed Region] AS 
(

SELECT 
    [State Province], 

    CASE [State Province] 
    WHEN 'Oregon' THEN 'West Coast' 
    WHEN 'Washington' THEN 'West Coast' 
    WHEN 'California' THEN 'West Coast' 
    ELSE 'Not West Coast' 
    END AS 'Region' 

FROM 
    [WideWorldImportersDW].[Dimension].[City] 
) 

SELECT 
    Region, 
    count(Region) AS Number, 
    ---THE PROBLEM LINE IS BELOW THIS--- 
    count(region)/(select count(*) FROM [WideWorldImportersDW].[Dimension]. 
    [City]) AS Percentage 


FROM 
    [Summed Region] 

GROUP BY 
    Region 

那条线的问题是什么？如果我将这两部分分开，每一部分都会返回正确的数字。但是当我将另一个分开时，我会得到'0'。

谢谢！

Answer 1

它被称为整数除法。所以加小数点地方：

SELECT Region, count(Region) AS Number, 
     count(region) * 1.0/(select count(*) FROM [WideWorldImportersDW].[Dimension].[City]) AS Percentage 
FROM [Summed Region] 
GROUP BY Region; 

你不需要的子查询要么，所以使用窗函数：

SELECT Region, count(*) AS Number, 
     count(*) * 1.0/sum(count(*)) over() AS Percentage 
FROM [Summed Region] 
GROUP BY Region;