我建议聚合两次：

select sum(total_amount), sum(collected_amount)
from (select transaction_id, total_amount, sum(collected_amount) as collected_amount
      from t
      group by transaction_id, total_amount
     ) tt;

对收集的数量求和将提供一个标量结果。分组和求和将在结果中提供n个记录，其中n等于唯一的事务ID。因此，不能将这两个查询组合成一个查询。但是，您可以运行嵌套查询，我通常避免这样做，因为这样做可能会降低效率。

select
    transaction_id,
    sum(total_amount),
    (select sum(collected_amount) from sample_table)
from sample_table
group by transaction_id;

另一种选择是尝试 OVER mysql中的语句。

select
    transaction_id,
    sum(total_amount) over(partition by transaction_id) total_amount,
    sum(collected_amount) over() collected_amount
from sample_table;