Percent calculation in Oracle SQL

Asked

Viewed 1,161 times

1

I’m mounting a query where I need the percentage of category for each cargo. If I filter for only one load, the query returns correctly. But if I put more than one (a period, for example) the percentage is calculated on all loads. It is possible to calculate the percentage individually?

Query:

select pcb.registro       
    ,pcb.data_entrada
    ,DECODE(tab.categ,'01','CANDIES','CHOCOLATE') Categoria
    ,SUM(tab.qtde) AS QTDE_CX
    ,ROUND(SUM(tab.Peso_Padrao),2) AS Peso_KG_PADRAO
    ,ROUND(SUM(tab.qtde) / SUM(SUM(tab.qtde)) OVER (),4)*100 AS PERCENTUAL_CARGA
--    ,ROUND(pcb.peso_liquido,2) PESO_PERC_BALANCA

FROM       

       (WITH w_cargas AS (SELECT DISTINCT wt.name           carga,
                                  wnd.delivery_id   distribuicao,
                                  ooha.order_number ordem
                             FROM oe_order_headers_all     ooha,
                                  oe_order_lines_all       oola,
                                  wsh_delivery_details     wdd,
                                  wsh_new_deliveries       wnd,
                                  wsh_delivery_assignments wda,
                                  wsh_trips                wt,
                                  wsh_trip_stops           wts1,
                                  wsh_delivery_legs        wdl
                            WHERE ooha.header_id         = oola.header_id
                              AND oola.cancelled_flag    = 'N'  -- ordem nao pode estar cancelada
                              AND oola.header_id         = wdd.source_header_id
                              AND oola.line_id           = wdd.source_line_id
                              AND wdd.source_code        = 'OE'
                              AND wdd.delivery_detail_id = wda.delivery_detail_id
                              AND wda.delivery_id        = wnd.delivery_id
                              AND wts1.trip_id           = wt.trip_id
                              AND wdl.pick_up_stop_id    = wts1.stop_id
                              AND wdl.delivery_id        = wnd.delivery_id
                              AND wnd.delivery_id        = wda.delivery_id)
SELECT wc.carga carga,
       sum(rctl.quantity_invoiced) qtde,
       sum(msi.unit_weight * (rctl.quantity_invoiced)) Peso_Padrao,
       mc.segment2 categ
  FROM ra_customer_trx_all         rct,
       ra_customer_trx_lines_all   rctl,
       mtl_system_items            msi,
       mtl_item_categories         mic,
       mtl_category_sets           mcs,
       mtl_categories              mc,
       w_cargas                    wc
 WHERE rctl.CUSTOMER_TRX_ID    = rct.CUSTOMER_TRX_ID
   and rctl.line_type          = 'LINE'
   and rct.status_trx          <> 'VD'
   and msi.inventory_item_id  = rctl.inventory_item_id
   and msi.organization_id    = 195
   and msi.inventory_item_id  = mic.inventory_item_id
   and msi.organization_id    = mic.organization_id
   and mic.category_set_id    = mcs.category_set_id
   and mcs.structure_id       = mc.structure_id
   and mic.category_id        = mc.category_id
   and mcs.category_set_name  = 'Vendas e Marketing'
   AND wc.distribuicao        = to_number(rct.interface_header_attribute3)
   AND RCT.trx_date BETWEEN '01-SET-2019' AND '25-SET-2019'
-- and wc.carga IN ('2228376','2230362') -- se tiver numero da carga, se nao comenta essa linha
-- and rct.trx_number = '387593'
  group by mc.segment2, wc.carga) tab,
  --PORTARIA
  pcn_pesagem_balanca pcb

  WHERE tab.carga IN (pcb.carga,pcb.carga2,pcb.carga3,pcb.carga4,pcb.carga5,pcb.carga6,pcb.carga7,pcb.carga8,pcb.carga9)

GROUP BY pcb.registro,
         DECODE(tab.categ,'01','CANDIES','CHOCOLATE'),
         pcb.data_entrada;

Exit with a load:
inserir a descrição da imagem aqui

Exit with multiple loads (period):
inserir a descrição da imagem aqui

1 answer

1


Should anyone from Oracle fall here, the solution was as follows:

On the line:

,ROUND(SUM(tab.qtde) / SUM(SUM(tab.qtde)) OVER (),4)*100 AS PERCENTUAL_CARGA

Placed PARTITION BY:

,ROUND(SUM(tab.qtde) / SUM(SUM(tab.qtde)) OVER (PARTITION BY pcb.registro),4)*100 AS PERCENTUAL_CARGA

Where he basically does the SUM for each group of records.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.