计算百分比的新列

Question

需要为每个"adh_classi“按"stop_code”ex添加一个包含百分比的列。

"Stop_code“计数adh_Classi

10013 32早

10013 101晚

10013 317准时

共计10013 =450个

早期-> 7.11% (32/450)

晚发-> 22.44% (101/450)

我没有太多的访问经验

Answer 1

在查询中完成查询需要一个聚合子查询或DSum()聚合函数来计算每个类的总数。

SELECT Stop_Code, 100 * Count / (SELECT Sum(Count) AS SumCnt FROM tablename AS Q1 
WHERE Q1.Stop_Code = tablename.Stop_Code) AS Pct FROM tablename;

或

SELECT tablename.Stop_Code, 100 * Count / SumCnt AS Pct 
FROM tablename 
INNER JOIN (SELECT Stop_Code, Sum(Count) AS SumCnt FROM tablename 
            GROUP BY Stop_Code) AS Q1 
ON tablename.Stop_Code = Q1.Stop_Code;

或

SELECT Stop_Code, 100 * Count / DSum("Count", "tablename", "Stop_Code=" & [Stop_Code]) AS Pct 
FROM tablename

域聚合函数在大型数据集中导致性能下降。

另一种方法是在组页脚：=Sum([Count])的文本框中构建使用分组和排序设计和聚合函数calc的报表。表达式详细部分将引用页脚文本框：=100 * [Count] / [tbxSubTotal]。