Y
Y
Yaroslav Timchenko2021-07-29 11:26:19
Oracle
Yaroslav Timchenko, 2021-07-29 11:26:19

How to group this query by three columns?

You need to group this query by columns DS.NAME, B.NAME, M.SHORT.

select  
    DS.NAME
     , B.NAME BRANDNAME
     , M.SHORT MEASUREMENT
     , (SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ
        JOIN DOCUMENTS D ON D.ID = FJ.R_IDDOCUMENT
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS WHERE DP.ID = DFP.ID AND D.DATE_ENTRY = '07.07.2021') BOTTLE_ON_BEGIN
     , (SELECT TO_NUMBER(DP.WEIGHT) FROM FGW_JOUR_MOVE FJ
        JOIN DOCUMENTS D ON D.ID = FJ.R_IDDOCUMENT
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS WHERE DP.ID = DFP.ID AND D.DATE_ENTRY = '07.07.2021') WEIGHT_ON_BEGIN
     , (SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 0 AND FJ.ID = FJM.ID) BOTTLE_ADD  
     , (SELECT TO_NUMBER(DP.WEIGHT) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 0 AND FJ.ID = FJM.ID) WEIGHT_ADD  
     , (SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 1 AND FJ.ID = FJM.ID) BOTTLE_MINUS
     , (SELECT TO_NUMBER(DP.WEIGHT) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 1 AND FJ.ID = FJM.ID) WEIGHT_MINUS
     , NVL((SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 0 AND FJ.ID = FJM.ID),0)
        - 
        NVL((SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ 
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS
        WHERE FJ.TYPEOPERATION = 1 AND FJ.ID = FJM.ID),0)
        +
        NVL((SELECT TO_NUMBER(DP.COUNT_BOTTLE) FROM FGW_JOUR_MOVE FJ
        JOIN DOCUMENTS D ON D.ID = FJ.R_IDDOCUMENT
        JOIN DOC_FORM15DOP_POS DP ON DP.ID = FJ.R_IDPOS WHERE DP.ID = DFP.ID AND D.DATE_ENTRY = '07.07.2021'),0)        
        BOTTLE_ON_END
      
 from (SELECT F.R_IDDOCUMENT FROM FGW_JOUR_MOVE F) T
               JOIN FGW_JOUR_MOVE FJM ON FJM.R_IDDOCUMENT = T.R_IDDOCUMENT
               JOIN DOCUMENTS D ON T.R_IDDOCUMENT = D.ID
               JOIN FGW_REST_PRODUCTS FRP ON FRP.ID = FJM.R_RESTPRODUCT
               JOIN DIC_STOIL2FAS DS ON DS.ID = FRP.R_IDDICSTOIL2FAS AND DS.NAME IN (SELECT NAME FROM DIC_STOIL2FAS)
               JOIN DOC_FORM15DOP_POS DFP ON DFP.ID = FJM.R_IDPOS
               JOIN SHIP.BRANDS B ON B.ID = FRP.R_IDBRAND
               JOIN GLOB.MEASUREMENTS M ON M.ID = FRP.R_IDMEASUREMENT
               WHERE D.DATE_ENTRY = '08.07.2021'

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question