Rollup in pivot query

Tod Goulds-Oracle

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_Gfrom (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"))

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

See the forum FAQ:

mathguy

While we wait for you to post usable sample data (CREATE TABLE and INSERT statements) and the desired output from that data, here's something you can think about.

PIVOT is very convenient. Are you familiar with how pivoting was done in the past (before version 11.1)? With CONDITIONAL AGGREGATION? Search for that term if you are not familiar with it, and you will see how that works for pivoting.

While PIVOT is very convenient, it isn't as flexible as conditional aggregation. In particular, when you need to roll up as well as pivot, it's often easier to use conditional aggregation - then you can do everything in a single query.

More than that after you post what you need to post.    Cheers!