WITH test_tab AS
(
SELECT 'DE_1' device, 'NO1' no, 10 qty, 'Y' hold, 'N' rework FROM dual
UNION ALL SELECT 'DE_1', 'NO2', 15, 'N', 'Y' FROM dual
UNION ALL SELECT 'DE_1', 'NO3', 10, 'N', 'N' FROM dual
UNION ALL SELECT 'DE_2', 'NO4', 10, 'Y', 'N' FROM dual
UNION ALL SELECT 'DE_2', 'NO5', 20, 'Y', 'N' FROM dual
UNION ALL SELECT 'DE_2', 'NO6', 25, 'N', 'N' FROM dual
UNION ALL SELECT 'DE_2', 'NO7', 15, 'N', 'N' FROM dual
)
SELECT CASE WHEN GROUP_ID() = 0 AND GROUPING(no) = 0 THEN device
            WHEN GROUP_ID() = 0 AND GROUPING(no) = 1 THEN 'TOTAL'
            ELSE 'NORMAL'
        END device
     , CASE WHEN GROUP_ID() = 0 AND GROUPING(no) = 0 THEN no
            WHEN GROUP_ID() = 0 AND GROUPING(no) = 1 THEN TO_CHAR(COUNT(*))
            ELSE TO_CHAR(COUNT(DECODE(hold||rework,'NN',1)))
        END no
     , CASE WHEN GROUP_ID() = 0 THEN SUM(qty)
            ELSE SUM(DECODE(hold||rework,'NN',qty))
        END qty
     , CASE WHEN GROUP_ID() = 0 AND GROUPING(no) = 0 THEN MIN(hold)
            WHEN GROUP_ID() = 0 AND GROUPING(no) = 1
            THEN COUNT(DECODE(hold,'Y',1))||'/'||SUM(DECODE(hold,'Y',qty))
        END hold
     , CASE WHEN GROUP_ID() = 0 AND GROUPING(no) = 0 THEN MIN(rework)
            WHEN GROUP_ID() = 0 AND GROUPING(no) = 1
            THEN COUNT(DECODE(rework,'Y',1))||'/'||SUM(DECODE(rework,'Y',qty))
        END rework
  FROM test_tab t
 GROUP BY device, ROLLUP(device, no)
 ORDER BY t.device, t.no, GROUP_ID()