Oracle SQL - SUM data based on a result from a nested query -

i have nested query supposed following:

1. bottom query creates column named "initiative" , flags records in column based on "bu_id".
2. then subqry, uses lag function check if records came 5 years back. based on "custid" , "initiative" (which defined in step 1).
3. finally, top query created column named "transaction_flag" flag records 'new' or 'existing'. query checks if there transactions 60 months back.

please see query below:

select "region" , "country" , "custid" , "customer" , "vertical" , "date_yearmonth" , "date_year" , "order_value" , "initiative" , max(case when to_date("prev_fym_initiative",'yyyymm')  >= add_months (to_date("date_yearmonth",'yyyymm'), -60) 'existing' else 'new' end) "transaction_flag" ( select  subqry."region" , subqry."country" , subqry."custid" , subqry."customer" , subqry."vertical" , subqry."date_yearmonth" , subqry."date_year" , subqry."order_value" , subqry."initiative" , lag (subqry."date_yearmonth", 1) on (partition subqry."custid", subqry."initiative" order "date_yearmonth" asc) "prev_fym_initiative" ( select         t1."region"         , t1."country"         , t1."custid"         , t1."customer"         , t1."vertical"         , t3."date_yearmonth"         , t3."date_year"         , t4."order_value"         , t3."date_desc"         , (case                     when t2."bu_id" in ('acc', 'lxc', 'otp') 'process'                     else 'not process' end) "initiative"                 "library"."factsales" t4         , "library"."customer_tbl" t1         , "library"."product_tbl" t2         , "library"."time_tbl" t3                 t4."customer_key" = t1."customer_key"         , t4."product_key" = t2."product_key"         , t4."date_key" = t3."date_key"         , t1."country" in ('austria', 'germany', 'france')         , t3."date_year" between '2012'and '2016'         , t4."order_value" > 0         group         t1."region"         , t1."country"         , t1."custid"         , t1."customer"         , t1."vertical"         , t3."date_yearmonth"         , t3."date_year"         , t4."order_value"         , t3."date_desc"         , t2."bu_id" ) subqry ) "initiative" 'process' group "region" , "country" , "custid" , "customer" , "vertical" , "date_yearmonth" , "date_year" , "order_value" , "initiative"; 

lets following result on example of single customer:

   region       country    custid       customer          vertical      date_yearmonth      date_year   order_value        initiative     transaction_flag 1. north         germany    25166       abraxo cleaner    chemicals     201201              2012        25.50              process        new 2. north         germany    25166       abraxo cleaner    chemicals     201201              2012        45.50              process        existing 3. north         germany    25166       abraxo cleaner    chemicals     201405              2014        73.49              process        existing 4. north         germany    25166       abraxo cleaner    chemicals     201507              2015        156.29             process        existing 5. north         germany     25166      abraxo cleaner    chemicals     201511              2015        376.22             process        existing 

as can see line no. 1 flagged 'new'. other lines flagged 'existing' because these booked in database later 1st line. however, line no. 2 booked same day.

what need achieve sum lines flagged 'new' lines 'existing' fall same date_yearmonth. going 5 lines above. need sum lines no. 1 , 2. total value of 71. or maybe there simple way flag such records, instead of summing them up? allow me sum these in excel when download results of query.

p.s. if that's of use, query used originates previous post: oracle sql- flag records based on record's date vs history.

change bottom query below.

you need fix other queries name change of order_value order_value_total. introduce order_count in other queries if want see it. bit easy figure out.

select     t1."region"     , t1."country"     , t1."custid"     , t1."customer"     , t1."vertical"     , t3."date_year"     , t3."date_yearmonth"     , t3."date_desc"     , (case                 when t2."bu_id" in ('acc', 'lxc', 'otp') 'process'                 else 'not process' end) "initiative"     , sum(t4."order_value") "order_value_total"     , count(*) "order_count"         "library"."factsales" t4     , "library"."customer_tbl" t1     , "library"."product_tbl" t2     , "library"."time_tbl" t3         t4."customer_key" = t1."customer_key"     , t4."product_key" = t2."product_key"     , t4."date_key" = t3."date_key"     , t1."country" in ('austria', 'germany', 'france')     , t3."date_year" between '2012'and '2016'     , t4."order_value" > 0     group     t1."region"     , t1."country"     , t1."custid"     , t1."customer"     , t1."vertical"     , t3."date_year"     , t3."date_yearmonth"     , t3."date_desc"     , (case             when t2."bu_id" in ('acc', 'lxc', 'otp') 'process'             else 'not process' end)