Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Rollup in pivot query

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"))
Answers
-
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).
-
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!