Как сгрупировать данный запрос по трём столбцам?

Question

[Ярослав Тимченко]

Нужно сгрупировать данный запрос по столбцам 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'

Comments

[Akina]

Ну и сгруппируйте. В чём проблема-то?

[Ярослав Тимченко]

Akina, Проблема в том, что если я просто пропишу
GROUP BY DS.NAME, B.NAME, M.SHORT
- это работать не будет

[Akina]

Ну правильно. Все остальные выходные поля надо обернуть агрегатными функциями.