如何使用子查询实现按shipping_service分组并对费用字段求和

实现方案 #

你可以直接在原有查询外层嵌套一层聚合逻辑，无需修改已经调试完成的内层查询，SQL写法如下：

SELECT 
    shipping_service,
    SUM(shipping_cost) AS total_shipping_cost,
    SUM(cobb_charge) AS total_cobb_charge
FROM (
    -- 原有查询逻辑
    SELECT 
        tsi.shipping_service,
        tsi.shipment_amount as shipping_cost,
        (
            SELECT sum(shipment_cost) FROM `tabPacking Slip packages shadow` sps 
            WHERE parent = 
                (SELECT name FROM `tabPacking Slip` pslip 
                    WHERE pslip.purchase_order = tsi.po_no LIMIT 1
                )
        ) as cobb_charge
    FROM tabSales Invoice as tsi
) AS t
GROUP BY shipping_service;

如果你希望一步查询出结果，也可以直接在原查询基础上添加聚合函数和分组规则，写法如下：

SELECT 
    tsi.shipping_service,
    SUM(tsi.shipment_amount) AS total_shipping_cost,
    SUM(
        (
            SELECT sum(shipment_cost) FROM `tabPacking Slip packages shadow` sps 
            WHERE parent = 
                (SELECT name FROM `tabPacking Slip` pslip 
                    WHERE pslip.purchase_order = tsi.po_no LIMIT 1
                )
        )
    ) AS total_cobb_charge
FROM tabSales Invoice as tsi
GROUP BY tsi.shipping_service;

输出结果 #

针对你提供的样例数据，聚合后的输出结果如下：

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