请求编写正确SQL：关联ORDERS与Transactions表实现聚合统计

现有ORDERS和Transactions两张业务表，表结构及数据如下：

ORDERS表 #

id   order_id   e_id   e_name 
1    1000       1001   Tom
2    1009       1001   Tom
3    1010       1001   Tom
4    1011       1002   Parker
5    1012       1002   Parker
6    1013       1003   Rohan

Transactions表 #

id  order_id  amount  status 
1   1000      100     success
2   1009      80      success      
3   1010      100     failed 
4   1011      50      success
6   1012      50      success
7   1013      100     failed

需要关联两表，查询得到包含以下字段的统计结果：

• e_id：员工ID
• e_name：员工姓名
• amount_sum：该员工所有订单的总金额
• total_counts：该员工的总订单数
• total_success_amount：该员工所有成功订单的总金额
• success_count：该员工的成功订单数

预期输出如下：

e_id   e_name  amount_sum  total_counts total_success_amount  success_count
  1001   Tom     280         3            180                   2
  1002   Parker  100         2            100                   2
  1003   Rohan   100         1            0                     0      

本人尝试的SQL语句如下，但未得到正确结果：

use card;
SELECT COUNT(orders.order_id) as `total_counts`, 
COUNT(CASE WHEN transactions.status = 'success' THEN 1 END) as `success_count`, 
SUM(0 + transactions.amount) as `amount_sum`, orders.e_id, 
orders.e_name 
FROM orders 
LEFT JOIN transactions  
ON transactions.order_id=orders.order_id
GROUP BY (orders.e_id), (orders.order_id),  
(orders.e_name), (transactions.amount), (transactions.status);

正确SQL语句 #

USE card;
SELECT 
    o.e_id,
    o.e_name,
    SUM(t.amount) AS amount_sum,
    COUNT(o.order_id) AS total_counts,
    COALESCE(SUM(CASE WHEN t.status = 'success' THEN t.amount END), 0) AS total_success_amount,
    COALESCE(COUNT(CASE WHEN t.status = 'success' THEN 1 END), 0) AS success_count
FROM orders o
LEFT JOIN transactions t ON t.order_id = o.order_id
GROUP BY o.e_id, o.e_name;

错误原因说明 #

原SQL的核心问题是GROUP BY子句包含了多余的字段：order_id、transactions.amount、transactions.status会导致按每个订单、每个金额、每个状态单独分组，无法实现按员工维度的聚合统计。正确的分组应该仅按员工的唯一标识（e_id和e_name）进行。

另外，使用COALESCE函数可以确保当没有成功订单时，total_success_amount和success_count返回0而非NULL，符合预期输出要求。

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