Oracle SQL: How to Sum groups with total of all groups

Question

What I am trying to do is basically manufacture a Sum of sums that only really makes sense when pivoted.

I am trying to analyze total system degradation, but also see it as Down and Degraded as separate call outs - this is where I am having trouble. In addition, if there was no value for a given period, it's assumed to be 0. I am trying to create a fictitious sum(a) + sum(b) column and I don't know how to do this

Here is the data, as it exists in the DB, when sum is applied on each category (I've explicitly introduced a year jump, and null values for impact are categorized as 'TBD'):

So, summary by week looks like:

CY	WEEK	IMPACT	DURATION_MINUTES
2022	50	2 - Degraded	175
2022	51	1 - Down	70
2022	51	2 - Degraded	522
2023	1	1 - Down	460
2023	1	2 - Degraded	1258
2023	2	1 - Down	58
2023	2	2 - Degraded	98

But, here is what I am shooting for:

CY	WEEK	0 - TBD	2 - Degraded	1 - Down	Total
2022	50	0	175	0	175
2022	51	0	522	70	592
2023	1	0	1258	460	1718
2023	2	0	98	58	156

Sample data:

CREATE TABLE MY_TABLE
(
    cy               INT          NOT NULL,
    week             INT          NOT NULL,
    IMPACT           VARCHAR2(12) NOT NULL,
    duration_minutes NUMBER       NOT NULL
);

INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 42);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 88);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 45);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '1 - Down', 70);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 86);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 220);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 216);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 29);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 62);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 369);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 58);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 42);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 277);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 881);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '1 - Down', 40);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '1 - Down', 18);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '2 - Degraded', 98);


commit;

And here is the select that produces the unpivoted data:

SELECT cy,
       week,
       impact,
       SUM(duration_minutes) AS duration_minutes
  FROM my_table
 GROUP BY cy, week, impact
 ORDER BY cy, week, impact;

Answer 1

You can use Conditional Sum Aggregation while removing the column impact from GROUP BY list such as

SELECT cy, week, 
       SUM(CASE WHEN impact IS NULL THEN duration_minutes ELSE 0 END) AS "0 - TBD",
       SUM(CASE WHEN impact = '2 - Degraded' THEN duration_minutes ELSE 0 END) AS "2 - Degraded",
       SUM(CASE WHEN impact = '1 - Down' THEN duration_minutes ELSE 0 END) AS "1 - Down",
       SUM(duration_minutes) AS duration_minutes
  FROM my_table
 GROUP BY cy, week
 ORDER BY 1, 2

Demo