Aggregations with GROUPING SETS, CUBE and ROLLUP

W

Whenever you need to multiple aggregations you should use GROUPING SETS or CUBE or ROLLUP. I like grouping sets, because they are more clear/readable and because often I don't need all aggregations/combinations.

WITH t AS (
    SELECT 'A' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 2 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 40 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 3 AS day_, 50 AS val FROM DUAL
)
SELECT
    t.group_name,
    t.day_,
    SUM(t.val) AS sum_val,
    --
    DECODE(GROUPING_ID(t.group_name, t.day_), 1, 1)     AS g1,
    DECODE(GROUPING_ID(t.group_name), 1, 1)             AS g2,
    DECODE(GROUPING_ID(t.day_), 1, 1)                   AS g3
FROM t
GROUP BY GROUPING SETS (
    (t.group_name, t.day_),     -- g1
    (t.group_name),             -- g2
    (t.day_),                   -- g3
    ()
)
ORDER BY 1, 2 NULLS LAST;

You can achieve same result with CUBE.

WITH t AS (
    SELECT 'A' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 2 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 40 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 3 AS day_, 50 AS val FROM DUAL
)
SELECT
    t.group_name,
    t.day_,
    SUM(t.val) AS sum_val,
    --
    DECODE(GROUPING_ID(t.group_name, t.day_), 1, 1)     AS g1,
    DECODE(GROUPING_ID(t.group_name), 1, 1)             AS g2,
    DECODE(GROUPING_ID(t.day_), 1, 1)                   AS g3
FROM t
GROUP BY CUBE (t.group_name, t.day_)
ORDER BY 1, 2 NULLS LAST;

You can even reduce CUBE sets.

WITH t AS (
    SELECT 'A' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 2 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 40 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 3 AS day_, 50 AS val FROM DUAL
)
SELECT
    t.group_name,
    t.day_,
    SUM(t.val) AS sum_val,
    --
    DECODE(GROUPING_ID(t.group_name, t.day_), 1, 1)     AS g1,
    DECODE(GROUPING_ID(t.group_name), 1, 1)             AS g2,
    DECODE(GROUPING_ID(t.day_), 1, 1)                   AS g3
FROM t
GROUP BY t.group_name, CUBE (t.day_)    -- reduce sets
ORDER BY 1, 2 NULLS LAST;

And you should explore ROLLUP too.

WITH t AS (
    SELECT 'A' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 2 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 2 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 3 AS day_, 40 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 3 AS day_, 50 AS val FROM DUAL
)
SELECT
    t.group_name,
    t.day_,
    SUM(t.val) AS sum_val,
    --
    DECODE(GROUPING_ID(t.group_name, t.day_), 1, 1)     AS g1,
    DECODE(GROUPING_ID(t.group_name), 1, 1)             AS g2,
    DECODE(GROUPING_ID(t.day_), 1, 1)                   AS g3
FROM t
GROUP BY ROLLUP(t.group_name, t.day_)
ORDER BY 1, 2 NULLS LAST;