Access SQL中count()与sum()计算结果异常问题求助

问题原因分析 #

1. 笛卡尔积问题：未关联Summary和Detail表时，数据库会生成两表所有记录的交叉组合，统计值会是两表记录数的乘积，导致结果异常偏大。
2. 工作天数统计错误：关联两表后，由于Summary（日工作记录）与Detail（工单/积分记录）是一对多关系，每条Summary记录会被重复匹配多次（对应多条Detail记录）。此时COUNT(Summary.DateOfOperation)实际统计的是关联后的总记录数（即Detail的记录数），而非Summary中的唯一工作天数。

解决办法 #

核心思路是先分别对两张表做聚合统计，再关联结果，避免一对多关系导致的重复计算：

1. 分步聚合再关联 #

假设两表通过DriverID关联，且需要按指定时段过滤：

SELECT 
    agg_summary.DriverID,
    agg_summary.WorkDays,
    agg_detail.TotalPoints,
    -- 处理除数为0的情况，可根据需求调整
    IIF(agg_summary.WorkDays = 0, 0, agg_detail.TotalPoints / agg_summary.WorkDays) AS AvgPointsPerDay
FROM
    -- 统计每个司机的工作天数
    (SELECT 
         DriverID,
         -- 若同一天有重复记录，改用 COUNT(DISTINCT DateOfOperation) 统计唯一天数
         COUNT(DateOfOperation) AS WorkDays
     FROM Summary
     WHERE DateOfOperation BETWEEN [StartDate] AND [EndDate]
     GROUP BY DriverID) AS agg_summary
INNER JOIN
    -- 统计每个司机的总积分
    (SELECT 
         DriverID,
         SUM(Points) AS TotalPoints
     FROM Detail
     -- 若Detail有时段字段，需同步过滤，避免统计非目标时段的积分
     WHERE WorkOrderDate BETWEEN [StartDate] AND [EndDate]
     GROUP BY DriverID) AS agg_detail
ON agg_summary.DriverID = agg_detail.DriverID;

2. 特殊场景处理 #

• 如果存在司机只有工作记录无积分，或只有积分无工作记录的情况，将INNER JOIN改为LEFT JOIN（保留所有有工作记录的司机）或RIGHT JOIN（保留所有有积分记录的司机）。
• 若Summary中同一司机同一天有多条重复记录，需用COUNT(DISTINCT DateOfOperation)替代COUNT(DateOfOperation)，确保统计的是唯一工作天数。

方案有效性说明 #

先对单表做聚合，得到每个司机的独立统计结果（工作天数、总积分），再关联这两个聚合结果，避免了一对多关系带来的记录重复，从而保证统计值的准确性。

内容的提问来源于stack exchange，提问作者Gilbert Kwok