3

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

2 Answers 2

4

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.

2
  • 1
    Nice idea, ROLLUP!
    – Littlefoot
    Feb 8 at 13:48
  • Thank you! This is exactly what I was looking for. I did see some examples about using ROLLUP but I was not familiar with it. I will definitely have to look at the documentation on how to use that in the future!
    – xentwo
    Feb 8 at 17:11
3

Here's one option (see lines #16 - 19).

Sample data:

SQL> with
  2    data(a, date_field, c, d) as (
  3      select 1, to_date( '01-JAN-2013'), 'hello',     0  from dual union all
  4      select 2, to_date( '01-FEB-2013'), 'wonderful', 10 from dual union all
  5      select 3, to_date( '20-MAR-2013'), 'world',     20 from dual union all
  6      select 4, to_date( '21-APR-2013'), 'tree',      10 from dual union all
  7      select 5, to_date( '21-JUN-2013'), 'tree',      30 from dual union all
  8      select 6, to_date( '01-JUL-2013'), 'world',     10 from dual union all
  9      select 7, to_date( '30-JUL-2013'), 'world',     20 from dual union all
 10      select 8, to_date( '30-JUL-2013'), 'are',       30 from dual union all
 11      select 9, to_date( '30-JUL-2013'), 'world',     30 from dual
 12    )
 13    ---

Query:

 14  select *
 15      from ( select c, to_char( date_field, 'dd' ) day, d,
 16                (select sum(x.d) from data x                                        --> this
 17                 where x.c = a.c                                                    -->
 18                   and to_char(x.date_field, 'MON') = to_char(a.date_field, 'MON')  -->
 19                ) total                                                             -->
 20                from data  a
 21               where to_char(date_field, 'MON') LIKE '%JUL%'
 22       ) pivot (
 23         sum(d) for day in (
 24  '01' AS "01",'02' AS "02",'03' AS "03",'04' AS "04",
 25  '05' AS "05",'06' AS "06",'07' AS "07",'08' AS "08",
 26  '09' AS "09",'10' AS "10",'11' AS "11",'12' AS "12",
 27  '13' AS "13",'14' AS "14",'15' AS "15",'16' AS "16",
 28  '17' AS "17",'18' AS "18",'19' AS "19",'20' AS "20",
 29  '21' AS "21",'22' AS "22",'23' AS "23",'24' AS "24",
 30  '25' AS "25",'26' AS "26",'27' AS "27",'28' AS "28",
 31  '29' AS "29",'30' AS "30",'31' AS "31"
 32      )
 33  );


C      TOTAL  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
----- ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
are       30                                                                                                                      30
world     60  10                                                                                                                  50
      ------
          ^
          |
       here it is

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.