按递减百分比排序

Question

代码：

SELECT [1_Tbl_Claim_Data].Customer, Sum([1_Tbl_Claim_Data].ClaimUSD) AS ClaimedAmount, Sum([1_Tbl_Claim_Data].POReimbursementAmountUSD) AS RmbtAmount, Format(Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD),"Fixed") AS ["%"]
FROM 1_Tbl_Claim_Data
GROUP BY [1_Tbl_Claim_Data].Customer
ORDER BY Format(Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD),"Fixed") DESC;

当我尝试按百分比表达式排序qry时，出现溢出错误。问题是有些报销金额是零，所以我猜这是造成溢出错误的原因，当一个数字被零整除时提前感谢

Answer 1

试试这个：

SELECT  Customer
,Sum(ClaimUSD) AS ClaimedAmount 
,Sum(POReimbursementAmountUSD) AS RmbtAmount
,Format(Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD),"Fixed") 
AS ["%"]
FROM 1_Tbl_Claim_Data
GROUP BY 1
ORDER BY 4 DESC;

Answer 2

您需要测试SUM(ClaimUSD)是否为0。

此外，您真的打算按字母顺序按百分比降序排序吗？

99%
9%
81%
22%
2%
12%
100%
10%
1%

我想你真的想按数值排序。

SELECT [1_Tbl_Claim_Data].Customer
, Sum([1_Tbl_Claim_Data].ClaimUSD) AS ClaimedAmount
, Sum([1_Tbl_Claim_Data].POReimbursementAmountUSD) AS RmbtAmount
, Format(
case
when sum(ClaimUSD) <> 0
then Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD)
end ,"Fixed") AS ["%"]
FROM 1_Tbl_Claim_Data
GROUP BY [1_Tbl_Claim_Data].Customer
ORDER BY case
when sum(ClaimUSD) <> 0
then Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD)
end DESC;

但我需要数据进行测试。此代码可能存在许多问题。

Answer 3

尝试按数值排序：

SELECT 
Customer, 
Sum(ClaimUSD) AS ClaimedAmount, 
Sum(POReimbursementAmountUSD) AS RmbtAmount, 
Sum(POReimbursementAmountUSD)/Sum(ClaimUSD) AS [Percent],
Format(Sum(POReimbursementAmountUSD)*100/Sum(ClaimUSD),"Fixed") AS ["%"]
FROM 
[1_Tbl_Claim_Data]
GROUP BY 
Customer
ORDER BY 
Sum(POReimbursementAmountUSD)/Sum(ClaimUSD) DESC;