How to group by and sum with sub query-CodePudding

i use this query below to get the data

              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;

OUTPUT

 ------------------------------------- --------------- ------------- 
| 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 |

i need to group this data by shipping service and sum all the cost and charge

CodePudding user response：

If your query produce the result in your output you can try:

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 ;