如何使用子查询进行分组和求和

Cris 发表于 Dev

克里斯

我使用下面的这个查询来获取数据

              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
                         )
                     GROUP BY tsi.shipping_service 
                 )
             as cobb_charge
             FROM tabSales Invoice as tsi;

输出

+-------------------------------------+---------------+-------------+
| shipping_service                    | shipping_cost | cobb_charge |
+-------------------------------------+---------------+-------------+
| UPS-Ground                          |     32.150000 |   32.150000 |
| UPS-Ground                          |      0.000000 |   18.150000 |
| UPS-Ground                          |     53.740000 |    0.000000 |
| UPS-Ground                          |     20.240000 |   20.240000 |
| UPS-Ground                          |     14.710000 |   14.710000 |
| UPS-Ground                          |     18.410000 |   18.410000 |
| UPS-Ground                          |     21.740000 |   21.740000 |

我需要按运输服务对这些数据进行分组，并将所有成本和费用相加

巴沙

如果您的查询在输出中产生结果，您可以尝试：

select     a.shipping_service,
           sum(a.shipping_cost),
           sum(a.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
                         )
                     GROUP BY tsi.shipping_service 
                 )
             as cobb_charge
             FROM tabSales Invoice as tsi      
) as a group by  a.shipping_service ;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。