Getting sum result for last year divided quarterly I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks.

Also, if it is possible, I want to have the type as a separate column with the type as the header.

This is the query I am using right now which is giving the result for last month.

Select 
  to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'MM') 
    || '-'
    || to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,

  case
     when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
          AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
      'AUM'
     when (AP.ASSET_TYPE like 'CFTD%') then
      'Depostis'
     when (AP.ASSET_TYPE like 'CASH%') then
      'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
      'Loan'
   end as Type,

   sum(ABS(AP.Eval_Market_Value)) as Total

from (select p.account,
           p.open_date    as Open_Date,
           ac.description as RM,
           s.*

      FROM k$portfolio p, k$client k
      LEFT JOIN k$client_role cr
        ON cr.client = k.id
       AND cr.role = 136
      LEFT JOIN k$actors ac
        ON cr.actor = ac.id,
     table(gtw$reporting.Statement(p.id,
                                      2,
                                      trunc(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1)),
                                      trunc(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1)),
                                      1,
                                      1002,
                                      1,
                                      'USD')) s
     wHERE s.line_type = 'P'
       and k.id = p.client
       and p.id = s.portfolio
       and p.portfolio_type = 'C'
       and p.status = 1

    ) ap


group by to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'YYYY'),

      case
        when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
             AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
         'AUM'
        when (AP.ASSET_TYPE like 'CFTD%') then
         'Depostis'
        when (AP.ASSET_TYPE like 'CASH%')  then
         'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
         'Loan'
      end


=====================

Current output



Desired result
I'm not sure if I understood the problem correctly, but maybe you're looking for something like this?

Select 
to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&amp;DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,
sum(case when (AP.ASSET_TYPE like 'CFTD%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Depostis',
sum(case when (AP.ASSET_TYPE like 'CASH%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Cash',
sum(case when (AP.ASSET_TYPE = 'FTL') then ABS(AP.Eval_Market_Value) else 0 end) as 'Loan',
...