### 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;
```