Refactoring the query

Question

Here is the example in fiddle.

I wonder if it would be possible to refactor the query below in a smarter way. The current query achieves the desired outcome; however, I am curious if it could be refactored further, perhaps by using a single ROLLUP and eliminating the need for unions. Below is the query.

WITH base_data AS (
    SELECT 
        p,
        id,
        v,
        1 AS Anzahl,
        0 AS Anzahl2,
        'M' AS Period
    FROM t 
    WHERE ts BETWEEN TRUNC(SYSDATE, 'MONTH') AND SYSDATE
    UNION ALL
    SELECT 
        p,
        id,
        v,
        0 AS Anzahl,
        1 AS Anzahl2,
        'Y' AS Period
    FROM t 
    WHERE ts BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE
)
SELECT 
    CASE 
        WHEN GROUPING(p) = 1 THEN 'Gesamt' 
        ELSE COALESCE(p, 'Unbekannt')
    END AS Art_der_Reklamation,
    SUM(Anzahl) AS Anzahl,
    SUM(Anzahl) * 100 / 30 AS AnteilMonat,
    SUM(Anzahl2) AS Anzahl2,
    SUM(Anzahl2) * 100 AS AnteilJahr
FROM base_data
GROUP BY ROLLUP(p)
UNION ALL
SELECT 
    CASE 
        WHEN GROUPING(id) = 1 THEN 'Gesamt' 
        ELSE COALESCE(TO_CHAR(id), 'Unbekannt')
    END AS Art_der_Reklamation,
    SUM(Anzahl) AS Anzahl,
    SUM(Anzahl) * 100 / 30 AS AnteilMonat,
    SUM(Anzahl2) AS Anzahl2,
    SUM(Anzahl2) * 100 AS AnteilJahr
FROM base_data
GROUP BY ROLLUP(id)
UNION ALL
SELECT 
    CASE 
        WHEN GROUPING(v) = 1 THEN 'Gesamt' 
        ELSE COALESCE(TO_CHAR(v), 'Unbekannt')
    END AS Art_der_Reklamation,
    SUM(Anzahl) AS Anzahl,
    SUM(Anzahl) * 100 / 30 AS AnteilMonat,
    SUM(Anzahl2) AS Anzahl2,
    SUM(Anzahl2) * 100 AS AnteilJahr
FROM base_data
GROUP BY ROLLUP(v);

Answer 1

You can use grouping by grouping sets ( ... ) to generate subtotals for each column and the overall total:

with rws as ( 
  select 
    t.*, 
    case when ts between trunc(sysdate, 'mm') and sysdate then 1 else 0 end as mm,
    case when ts between trunc(sysdate, 'yyyy') and sysdate then 1 else 0 end as yyyy
  from t
  where ts between trunc(sysdate, 'yyyy') and sysdate
)
select CASE 
        WHEN GROUPING(id) = 0 THEN COALESCE(TO_CHAR(id), 'Unbekannt') 
        WHEN GROUPING(p) = 0 THEN COALESCE(TO_CHAR(p), 'Unbekannt')
        WHEN GROUPING(v) = 0 THEN COALESCE(TO_CHAR(v), 'Unbekannt')
        ELSE 'Gesamt'
    END v,
    SUM(mm) AS Anzahl,
    SUM(mm) * 100 / 30 AS AnteilMonat,
    SUM(yyyy) AS Anzahl2,
    SUM(yyyy) * 100 AS AnteilJahr
from rws
group by grouping sets ( (p), (id), (v), () )