select to_char(record_date,'YYYY-MM-DD') as "name",
decode('U','U','Upgrade','D','Downgrade') as "direction",
'ALL' as "portfolio_id"
,
avg(total_mv) as "marketValue",
avg(total_sv) as "statValue",
avg(total_gv) as "gaapValue",
avg(total_par) as "par",
count(*) as "naic_shifts",

(
    select ccmsectype as "name",
    decode('U','U','Upgrade','D','Downgrade') as "direction",
		sum(marketvalue) as "marketValue",
		sum(statvalue) as "statValue",
		sum(gaapvalue) as "gaapValue",
		sum(par) as "par",
		count(*) as "naic_shifts"
		from naic_up_down_view inner where 
		outer.record_date = inner.record_date and 
		( 
       ( 
          (DECODE ('U','U',1,0)=1 AND ORG_NAIC>CURRENT_NAIC)
          OR (DECODE('U', 'D' , 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)   
        )

			)	group by ccmsectype
     
)

from  naic_up_down_view outer 
where
( 
    ( decode ('U','U',1,0)=1 and org_naic>current_naic) OR 
		( decode ('U','D',1,0)=1 and org_naic<current_naic)   
) 
group by record_date