With TC as(

select  /*+ NO_MERGE(T1) NO_MERGE(T2)
PARALLEL(T1,4) PARALLEL(T2,4) */

T1.* from (

select /*+
DRIVING_SITE(A) NO_MERGE(A) NO_MERGE(fips) NO_MERGE(fw) PARALLEL(A,4)
PARALLEL(fips,4) PARALLEL(fw,4) */

     distinct


resource_value,


resource_type,

     L3_IMSI as IMSI

    ,L9_Calling_Number as MDN

    ,L9_ECID as Curr_ECID

    ,trunc(START_TIME) as Usage_Date

    ,trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')) as enodeb

    ,substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2) as fips_cd

    ,STATE_CODE

    ,STATE_NAME

    ,Sum(L3_ROUNDED_UNIT/1024) as MB_Usage





from RT_ET A

INNER JOIN

FIPS_STATEfips

ON trim(to_char(fips.FIPS_CODE,'00')) = substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (A.L9_ECID,1,LENGTH(A.L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2)

INNER JOIN

DVC_ADDRfw

ON trim(L3_IMSI) = trim(to_char(FW.IMSI))

Where A.L9_ECID not in (' ','0') AND A.L3_IMSI not in (' ','0')

AND trunc(A.start_time) > trunc(sysdate-8)

AND trunc(start_time) > trunc(FW.ODS_INSERT_DATE)



group by


resource_value

    ,resource_type

    ,L3_IMSI

    ,L9_Calling_Number

    ,L9_ECID

    ,trunc(START_TIME)

    ,trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000'))

    ,substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2)

    ,STATE_CODE

    ,STATE_NAME



) T1

where NOT EXISTS (

SELECT

1 FROM  DVC_ENODEB T2

    WHERE T1.IMSI = trim(to_char(T2.IMSI))

    AND T1.MDN = T2.MDN

            AND T1.ENODEB = T2.ENODEB

)

)







select

distinct

     SITES.*,

   TC.resource_value,

TC.resource_type,

TC.IMSI,

TC.MDN,

TC.Curr_ECID,

TC.Usage_Date,

TC.enodeb,

TC.fips_cd,

TC.STATE_CODE,

TC.STATE_NAME,

**TC.MB_Usage   -- Need to apply Sum(TC.MB_Usage)**



from

(



with

endb as

(select

  e.IMSI, e.MDN,e.site_id, E.ENODEB,

  ROW_NUMBER ()OVER (PARTITION BY e.IMSI||e.MDN

ORDER BY e.IMSI||e.MDN )  row_id

  from
FIXED_WIRELESS_DVC_ADDR_ENODEB e



)

select *



  from (select IMSI,MDN,IMSI||MDN as IMSI_MDN,site_id,row_id



          from endb



       )



pivot (max(site_id) siteid for row_id in (1,2,3,4,5,6,7,8,9,10,11,12,13)) ) SITES



INNER JOIN TC

ON ((TC.IMSI = trim(to_char(SITES.IMSI))) AND TC.MDN = SITES.MDN)





**Cannot able to apply Group by
based on below PIVOT columns (SITES.*), if I use SUM for MD_USAGE







Group BY

TC.resource_value,

TC.resource_type,

TC.IMSI,

TC.MDN,

TC.Curr_ECID,

TC.Usage_Date,

TC.enodeb,

TC.fips_cd,

TC.STATE_CODE,

TC.STATE_NAME,

SITES.***