How can I add a subtotal of specific column values?

Question

Here is the DATASET:

and here is the SQL I have:

select f.DATE, f.PROD_STATUS,
count (*)  AS TOTAL
from PROD_TABLE  f
where DATE = '04-MAY-17'
GROUP BY f.DATE, f.PROD_STATUS

I'm trying to get the value for 'SUCCESS' as a column in the SQL Results:

(SUCCESS = READY_1 + READY_2 + READY_3 + READY_4 + READY_5)

I want the SQL results to look like this:

.

How can I achieve that?

Answer 1

Check this:

with t as (
  select 1 as ready_1, 
         2 as ready_2, 
         3 as ready_3, 
         1 as in_process,
         4 as fail,
         5 as crash,
         '5/4/2017' as dat   
  from dual 
  union all
  select 2 as ready_1, 
         2 as ready_2, 
         3 as ready_3, 
         1 as in_process,
         4 as fail,
         0 as crash,
         '5/5/2017' as dat   
  from dual 
) 

select dat, prod_stat, max(suc) over(partition by dat) as success, sum(value) over(partition by dat) as total
from (
  select dat, prod_stat, value, sum(value) over (partition by dat) as suc
  from t 
  unpivot(   
    value for prod_stat in (ready_1, ready_2, ready_3) 
  )

union all

  select dat, prod_stat, value, null as suc
  from t 
  unpivot(   
    value for prod_stat in (in_process, fail, crash) 
  )
)

Result:

DAT         PROD_STAT   SUCCESS TOTAL
5/4/2017    READY_2        6    16
5/4/2017    READY_1        6    16
5/4/2017    CRASH          6    16
5/4/2017    FAIL           6    16
5/4/2017    IN_PROCESS     6    16
5/4/2017    READY_3        6    16
5/5/2017    FAIL           7    12
5/5/2017    IN_PROCESS     7    12
5/5/2017    CRASH          7    12
5/5/2017    READY_2        7    12
5/5/2017    READY_1        7    12
5/5/2017    READY_3        7    12