How can I compute the TOTAL of multiple COLUMNS from a PIVOT?

I have the following ORACLE SQL table/data:

A
DATE_FIELD
C
D

1
01-JAN-13
hello
0

2
01-FEB-13
wonderful
10

3
20-MAR-13
world
20

4
21-APR-13
tree
10

5
21-JUN-13
tree
30

6
01-JUL-13
world
10

7
30-JUL-13
world
20

8
30-JUL-13
are
30

9
30-JUL-13
world
30

With an example query I'm working with:

with 
  data(a, date_field, c, d) as (
    select 1, to_date( '01-JAN-2013'), 'hello',     0  from dual union all
    select 2, to_date( '01-FEB-2013'), 'wonderful', 10 from dual union all
    select 3, to_date( '20-MAR-2013'), 'world',     20 from dual union all
    select 4, to_date( '21-APR-2013'), 'tree',      10 from dual union all
    select 5, to_date( '21-JUN-2013'), 'tree',      30 from dual union all
    select 6, to_date( '01-JUL-2013'), 'world',     10 from dual union all
    select 7, to_date( '30-JUL-2013'), 'world',     20 from dual union all
    select 8, to_date( '30-JUL-2013'), 'are',       30 from dual union all
    select 9, to_date( '30-JUL-2013'), 'world',     30 from dual
  )
  ---
select *
    from ( select c, to_char( date_field, 'dd' ) day, d
              from data 
             where date_field LIKE '%JUL%'  
     ) pivot ( 
       sum(d) for day in ( 
'01' AS "01",/**'02' AS "02",'03' AS "03",'04' AS "04", 
'05' AS "05",'06' AS "06",'07' AS "07",'08' AS "08", 
'09' AS "09",'10' AS "10",'11' AS "11",'12' AS "12", 
'13' AS "13",'14' AS "14",'15' AS "15",'16' AS "16", 
'17' AS "17",'18' AS "18",'19' AS "19",'20' AS "20", 
'21' AS "21",'22' AS "22",'23' AS "23",'24' AS "24", 
'25' AS "25",'26' AS "26",'27' AS "27",'28' AS "28", 
'29' AS "29",**/'30' AS "30",'31' AS "31" 
    ) 
)

C
01
30
31

are
null
30
null

world
10
50
null

DB FIDDLE LINK

How can I add a column with the SUM TOTAL of COLUMNS 01,30,31 by modifying the PIVOT or perhaps a using LEFT JOIN? I've been scratching my head with this for almost 5 hours now :(.

Something that would look like:

C
01
30
31
TOTAL

are
null
30
null
30

world
10
50
null
60

Another option is to use the ROLLUP GROUP BY function:

with 
  data(a, date_field, c, d) as (
    select 1, to_date( '01-JAN-2013'), 'hello',     0  from dual union all
    select 2, to_date( '01-FEB-2013'), 'wonderful', 10 from dual union all
    select 3, to_date( '20-MAR-2013'), 'world',     20 from dual union all
    select 4, to_date( '21-APR-2013'), 'tree',      10 from dual union all
    select 5, to_date( '21-JUN-2013'), 'tree',      30 from dual union all
    select 6, to_date( '01-JUL-2013'), 'world',     10 from dual union all
    select 7, to_date( '30-JUL-2013'), 'world',     20 from dual union all
    select 8, to_date( '30-JUL-2013'), 'are',       30 from dual union all
    select 9, to_date( '30-JUL-2013'), 'world',     30 from dual
  )
  ---
select *
    from ( select c, coalesce( to_char( date_field, 'dd' ), 'TOTAL' ) day, sum(d) d
              from data
             where date_field LIKE '%JUL%'
           group by c, rollup( to_char( date_field, 'dd' ) )
     ) pivot ( 
       sum(d) for day in ( 
'01' AS "01",/**'02' AS "02",'03' AS "03",'04' AS "04", 
'05' AS "05",'06' AS "06",'07' AS "07",'08' AS "08", 
'09' AS "09",'10' AS "10",'11' AS "11",'12' AS "12", 
'13' AS "13",'14' AS "14",'15' AS "15",'16' AS "16", 
'17' AS "17",'18' AS "18",'19' AS "19",'20' AS "20", 
'21' AS "21",'22' AS "22",'23' AS "23",'24' AS "24", 
'25' AS "25",'26' AS "26",'27' AS "27",'28' AS "28", 
'29' AS "29",**/'30' AS "30",'31' AS "31", 'TOTAL' AS "TOTAL"
    ) 
)

C
01
30
31
TOTAL

are
null
30
null
30

world
10
50
null
60

fiddle

With the help of group by c, rollup(to_char( date_field, 'dd' )), the derived table additionally produces TOTAL rows for each c. Adding a declaration for the TOTAL column to the PIVOT list ensures that it is included in the output.

StackExchange Database Administrators Q#323207, answer score: 4