我很难从带有别名的列中计算百分比

Question

我正在使用子查询来计算具有别名的列上的百分比，并且我很难弄清楚为什么sum()函数不会对列中的值求和。它只是把数字除以数字本身，然后乘以100，所以实际上，每个总数等于100。编辑:我不得不削减查询之前，我的从和适当括号组的子句。查询工作，但是对于所有百分比返回100。

Select A.[DataCollection Period Report Year], A.[Section Name], A.[Form Name], A.Amount, 
(A.Amount / sum(A.Amount)) * 100 as Percentage
from
(
Select distinct [DataCollection Period Report Year],
RFD.[Section Name],   
RFD.[Form Name],
sum(cast(RFD.Answer as float)) as Amount        `

Answer 1

我相信您需要在子查询和初始查询中使用group by语句。此外，您的子查询缺少FROM语句。你还需要保持你的别名一致。

SELECT A."DataCollection Period Report Year",
A."Section Name",
A."Form Name",
A.Answer,
(A.Answer / sum(temp.Amount)) * 100 AS Percentage
FROM myTable A
join (
SELECT DISTINCT RFD."DataCollection Period Report Year",
sum(cast(RFD.Answer AS FLOAT)) AS Amount
FROM myTable RFD
group by 
"DataCollection Period Report Year"
) temp
GROUP BY 
A."DataCollection Period Report Year",
A."Section Name",
A."Form Name",
A.Answer