Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (id, value) AS
SELECT 'a', 1 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 'b', 1 FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 'c', 1 FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'd', 1 FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'e', 1 FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 'f', 1 FROM DUAL CONNECT BY LEVEL <=  9 UNION ALL
SELECT 'g', 1 FROM DUAL CONNECT BY LEVEL <= 23;
59 rows affected

SELECT COALESCE(
         CASE 
         WHEN id IN ('a','d') THEN '1'
         WHEN id IN ('b','e') THEN '2'
         WHEN id IN ('c','f') THEN '3'
         END,
         'all'
       ) AS grp,
       SUM(value) AS total_value
FROM   table_name
WHERE  id IN ('a','b','c','d','e','f')
GROUP BY
       ROLLUP(
         CASE 
         WHEN id IN ('a','d') THEN '1'
         WHEN id IN ('b','e') THEN '2'
         WHEN id IN ('c','f') THEN '3'
         END
       )


GRP
TOTAL_VALUE


1
15

2
9

3
12

all
36