Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Rollup in pivot query

Tod Goulds-OracleMay 21 2020 — edited May 21 2020

Hi team,

so I have a query (below) that shows a list exactly how I need it... it displays perfectly... however now I need to show totals at the "Region" and "Team" level... but I can't work out how to do it... any guidance would be great...

I'm still learning SQL... so any examples within the pivot below would be awesome...

select 'QL1' as QL,

SORT_ORDER,

REGION,

TEAM,

TERRITORY,

PRIME_REP,

EMAIL_ADDERSS,

"Q1 Credit",

"Q1_Target",

round((nvl("Q1 Credit",0)/"Q1_Target")*100) as "Q1 % Attainment",

"Q2 Credit",

"Q2_Target",

round((nvl("Q2 Credit",0)/"Q1_Target")*100) as "Q2 % Attainment",

"Q3 Credit",

"Q3_Target",

round((nvl("Q3 Credit",0)/"Q3_Target")*100) as "Q3 % Attainment",

"Q4 Credit",

"Q4_Target",

round((nvl("Q4 Credit",0)/"Q4_Target")*100) as "Q4 % Attainment",

FY_QUOTA,

nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0) as "FY Credit",

round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100) as "FY % Attainment",

to_number(CASE WHEN round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100) >= 100 THEN '100'

       else to_char(round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100))

       END) as FY_Attainment_G

from (select SP_H_SORT_ORDER as SORT_ORDER,

    SP_H__REGION as REGION,

    SP_H__TEAM as TEAM,

    SP_BOOKINGS.TERRITORY as TERRITORY,

    SP_BOOKINGS.QTR as QTR_YR,  

    SP_BOOKINGS.PRIME_REP as PRIME_REP,

      SP_BOOKINGS.EMAILADDRESS as EMAIL_ADDERSS,

      SP_FY_TARGET.FY_QUOTA as FY_QUOTA,

     round(SP_FY_TARGET.Q1_Target) as "Q1_Target",

    round(SP_FY_TARGET.Q2_Target) as "Q2_Target",

    round(SP_FY_TARGET.Q3_Target) as "Q3_Target",

    round(SP_FY_TARGET.Q4_Target) as "Q4_Target",

    sum(round(TO_NUMBER(SP_BOOKINGS.SALES_CREDIT))) as SALES_CREDIT    

     from SP_BOOKINGS SP_BOOKINGS,

      SP_FY_TARGET SP_FY_TARGET,

      SP_HRCHY SP_HRCHY

     

      where SP_HRCHY.SP_H_T_OWNER_EMAIL=SP_BOOKINGS.EMAILADDRESS and LOWER(SP_FY_TARGET.EMPLOYEE)=LOWER(SP_BOOKINGS.EMAILADDRESS) and 

      (:P448_GLOBAL =1 or LOWER(SP_BOOKINGS.EMAILADDRESS) = LOWER(:P448_CURRENT_EMAIL)) and (SP_BOOKINGS.REGION is not null and SP_BOOKINGS.DIRECTOR is not null and SP_BOOKINGS.PRIME_REP is not null)

     

group by  SP_HRCHY.SP_H_SORT_ORDER, SP_HRCHY.SP_H__REGION, SP_HRCHY.SP_H__TEAM,               

      SP_BOOKINGS.TERRITORY, SP_BOOKINGS.QTR, SP_BOOKINGS.PRIME_REP, SP_BOOKINGS.EMAILADDRESS,

      SP_FY_TARGET.FY_QUOTA,

      SP_FY_TARGET.Q1_Target,

      SP_FY_TARGET.Q2_Target,

      SP_FY_TARGET.Q3_Target,

      SP_FY_TARGET.Q4_Target

     )

PIVOT ( SUM (SALES_CREDIT)

      for QTR_YR

      IN ('Q120' as "Q1 Credit",

          'Q220' as "Q2 Credit",

          'Q320' as "Q3 Credit",

          'Q420' as "Q4 Credit"))

Comments

Post Details

Added on May 21 2020
2 comments
616 views