如何按Agent分组，统计不同FILE_ID的AMT总和及产品数量？

问题核心是父表的交易金额会因关联子表的多条产品记录被重复计算，所以必须拆分统计逻辑：先单独计算代理的销售总额（从父表直接分组），再统计对应的产品总数（关联父子表后统计），最后将两个结果合并。

解法1：子查询关联 #

SELECT
    tf.agent,
    tf.total_amt,
    COALESCE(tp.total_products, 0) AS total_products
FROM
    -- 先统计每个代理的销售总额（直接从父表分组，避免重复计算）
    (SELECT agent, SUM(amt) AS total_amt
     FROM transaction_file
     GROUP BY agent) tf
LEFT JOIN
    -- 统计每个代理对应的产品总数（关联父子表后计数）
    (SELECT tf.agent, COUNT(tp.id) AS total_products
     FROM transaction_product tp
     JOIN transaction_file tf ON tp.parent_file = tf.id
     GROUP BY tf.agent) tp
ON tf.agent = tp.agent;

解法2：CTE（公共表表达式） #

这种写法逻辑更清晰，适合复杂场景：

WITH agent_total_amt AS (
    -- 单独计算代理的销售总额
    SELECT
        agent,
        SUM(amt) AS total_amt
    FROM transaction_file
    GROUP BY agent
),
agent_product_total AS (
    -- 统计代理的产品总数
    SELECT
        tf.agent,
        COUNT(tp.id) AS total_products
    FROM transaction_product tp
    INNER JOIN transaction_file tf ON tp.parent_file = tf.id
    GROUP BY tf.agent
)
-- 合并两个统计结果
SELECT
    ata.agent,
    ata.total_amt,
    COALESCE(apt.total_products, 0) AS total_products
FROM agent_total_amt ata
LEFT JOIN agent_product_total apt ON ata.agent = apt.agent;

为什么之前的方法错误？ #

• 使用SUM(DISTINCT File.amt)：若不同交易文件的金额相同，会被去重，导致统计的总额偏小。
• 使用SUM(File.amt)：关联子表后，父表的单条交易记录会被重复匹配多次（等于对应产品数），金额被重复累加，导致总额偏大。

内容的提问来源于stack exchange，提问作者Matthew Carter