Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PL/SQL Get Sub-Totals and Grand Total Row

Posted on 2010-08-30
8
Medium Priority
?
2,815 Views
Last Modified: 2012-05-10
When I run the following code, the output is exactly what I want - except I also need a Grand Total row as the last row in the result set. How do I modify the code to keep the output like it is, and just add a new last row for the grand totals (total of the subtotal rows, or just a total of all the record rows (excluding the sub-total rows) of the three columns that have sub-totals? I've attached a pdf of how the output looks now; I just need a final grand total row added to it.

Also, in the three amount columns, for the values that show as '(null)', how do I make the word '(null)' into a blank so the cell will be blank, and still calculate the cell value as a numeric zero for each of the record rows? I still want a zero to appear in the sub-total and grand total rows if any of the three columns being sub-totaled equals '(null)' - as it does in the second sub-total in the attachment.

SELECT CASE WHEN GROUPING(PLAN_ID) = 1 THEN 'SUB-TOTALS' ELSE PLAN_ID END PLAN_ID, NVL(DIVISION_ID,' ') DIVISION_ID,
NVL(SSN, ' ') SSN, NVL(LAST_NAME, ' ') LAST_NAME, NVL(FIRST_NAME, ' ') FIRST_NAME, NVL(MI, ' ') MI, NVL(DOB, ' ') DOB, NVL(GENDER, ' ') GENDER,
NVL(MARITAL_SC, ' ') MARITAL_SC, NVL(ADDRESS_1, ' ') ADDRESS_1, NVL(ADDRESS_2, ' ') ADDRESS_2, NVL(CITY, ' ') CITY, NVL(STATE, ' ') STATE,
NVL(ZIP, ' ') ZIP, NVL(DOH, ' ') DOH, NVL(DOT, ' ') DOT, NVL(REHIRE_DT, ' ') REHIRE_DT, NVL(END_PR_DT, ' ')END_PR_DT, SUM(EMP_DEFER) EMP_DEFER,
SUM(EMPR_MATCH) EMPR_MATCH, NVL(to_char(to_number(EE_DISCRET),9999.99), ' ') EE_DISCRET, SUM(LOAN_PMT_1) LOAN_PMT_1,
NVL(to_char(LOAN_PMT_2), ' '), NVL(to_char(to_number(YTD_HRS),9999), ' '),
NVL(to_char(to_number(YTD_TTL_CMP),999999.99), ' ') YTD_TTL_CMP, NVL(to_char(to_number(YTD_PLN_CMP),999999.99), ' ') YTD_PLN_CMP,
NVL(to_char(to_number(PP_ENT_CMP),999999.99), ' ') PP_ENT_CMP, NVL(to_char(to_number(HCE_STAT),9999999999), ' ') HCE_STAT,
NVL(to_char(to_number(PCT_OWN),9999999999), ' ') PCT_OWN, NVL(to_char(to_number(EXCL_CLASS),9999999999), ' ') EXCL_CLASS,
NVL(to_char(to_number(OFC_DETRM),9999999999), ' ')OFC_DETRM, NVL(PTP_ELG_DT, ' ') PTP_ELG_DT,
NVL(to_char(to_number(ELIG_CLASS),9999999999), ' ')ELIG_CLASS, NVL(to_char(to_number(BT_CNTR_PCT),9999999999), ' ') BT_CNTR_PCT,
NVL(to_char(to_number(BT_CNTR_AMT),9999999999), ' ') BT_CNTR_AMT, NVL(to_char(to_number(AT_CNTR_PCT),9999999999), ' ')AT_CNTR_PCT,
NVL(to_char(to_number(AT_CNTR_AMT),9999999999), ' ')AT_CNTR_AMT, NVL(to_char(to_number(PAYPD_SAL),9999999999), ' ')PAYPD_SAL,
NVL(PAYROLL_FRQ, ' ')PAYROLL_FRQ  
FROM (
SELECT *
FROM KSEND
)

GROUP BY GROUPING SETS ((DIVISION_ID), (PLAN_ID, DIVISION_ID, SSN, LAST_NAME, FIRST_NAME,
MI, DOB, GENDER, MARITAL_SC, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, DOH, DOT, REHIRE_DT, END_PR_DT, EE_DISCRET,
LOAN_PMT_2, YTD_HRS, YTD_TTL_CMP, YTD_PLN_CMP, PP_ENT_CMP, HCE_STAT, PCT_OWN, EXCL_CLASS, OFC_DETRM,
PTP_ELG_DT, ELIG_CLASS, BT_CNTR_PCT, BT_CNTR_AMT, AT_CNTR_PCT, AT_CNTR_AMT, PAYPD_SAL, PAYROLL_FRQ,
PR_ENDING_DATE))
ORDER BY DIVISION_ID, 1, LAST_NAME;
PLSQL-Totals-Question.pdf
0
Comment
Question by:Glenn Stearns
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:anumoses
ID: 33559348
What tool, do you use to run your report? Is it oracle reports or any other reporting tool?
0
 
LVL 2

Expert Comment

by:Subramanyeswara
ID: 33559555
By using union all command add one more  grand total record atlast of the above query.
0
 

Author Comment

by:Glenn Stearns
ID: 33559700
I'm using Oracle SQL Developer, database is 9i. The pdf file is just a copy of the output from the query when it is run in Oracle SQL Developer. I'm not using any reporting tool.


What's the syntax of the UNION ALL command you would use? You're saying it would go after ORDER BY DIVISION_ID, 1, LAST_NAME; ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:Subramanyeswara
ID: 33559880
yes, it is after order by DIVISION_ID, 1, LAST_NAME;

As per below:
replace select 'x'  from dual order by 1 with above query and
select 'y'  from dual;  write grand total query .
select * from (select 'x'  from dual order by 1)
union all
select 'y'  from dual;
0
 

Author Comment

by:Glenn Stearns
ID: 33560068
subramanyes...

You'll have to pardon my relative ignorance with PL/SQL.  Are you saying to replace 'SELECT 'X' FROM DUAL ORDER BY 1' with my entire query script? Or, is 'REPLACE SELECT 'X' FROM DUAL ORDER BY 1' a command line that I need to put after the ORDER BY statement and run it as you wrote it?

Then you're saying I write another query to get the grand total and put that query in next?

I understand the last three lines starting with SELECT * FROM (SELECT 'X' FROM DUAL...

I just can't figure out exactly what you're telling me to do in the first three lines.

0
 
LVL 2

Accepted Solution

by:
Subramanyeswara earned 2000 total points
ID: 33560513
try the below query logic, check the grand total calcualtion?
select * from (SELECT CASE WHEN GROUPING(PLAN_ID) = 1 THEN 'SUB-TOTALS' ELSE PLAN_ID END PLAN_ID, NVL(DIVISION_ID,' ') DIVISION_ID,
NVL(SSN, ' ') SSN, NVL(LAST_NAME, ' ') LAST_NAME, NVL(FIRST_NAME, ' ') FIRST_NAME, NVL(MI, ' ') MI, NVL(DOB, ' ') DOB, NVL(GENDER, ' ') GENDER,
NVL(MARITAL_SC, ' ') MARITAL_SC, NVL(ADDRESS_1, ' ') ADDRESS_1, NVL(ADDRESS_2, ' ') ADDRESS_2, NVL(CITY, ' ') CITY, NVL(STATE, ' ') STATE,
NVL(ZIP, ' ') ZIP, NVL(DOH, ' ') DOH, NVL(DOT, ' ') DOT, NVL(REHIRE_DT, ' ') REHIRE_DT, NVL(END_PR_DT, ' ')END_PR_DT, SUM(EMP_DEFER) EMP_DEFER,
SUM(EMPR_MATCH) EMPR_MATCH, NVL(to_char(to_number(EE_DISCRET),9999.99), ' ') EE_DISCRET, SUM(LOAN_PMT_1) LOAN_PMT_1,
NVL(to_char(LOAN_PMT_2), ' '), NVL(to_char(to_number(YTD_HRS),9999), ' '),
NVL(to_char(to_number(YTD_TTL_CMP),999999.99), ' ') YTD_TTL_CMP, NVL(to_char(to_number(YTD_PLN_CMP),999999.99), ' ') YTD_PLN_CMP,
NVL(to_char(to_number(PP_ENT_CMP),999999.99), ' ') PP_ENT_CMP, NVL(to_char(to_number(HCE_STAT),9999999999), ' ') HCE_STAT,
NVL(to_char(to_number(PCT_OWN),9999999999), ' ') PCT_OWN, NVL(to_char(to_number(EXCL_CLASS),9999999999), ' ') EXCL_CLASS,
NVL(to_char(to_number(OFC_DETRM),9999999999), ' ')OFC_DETRM, NVL(PTP_ELG_DT, ' ') PTP_ELG_DT,
NVL(to_char(to_number(ELIG_CLASS),9999999999), ' ')ELIG_CLASS, NVL(to_char(to_number(BT_CNTR_PCT),9999999999), ' ') BT_CNTR_PCT,
NVL(to_char(to_number(BT_CNTR_AMT),9999999999), ' ') BT_CNTR_AMT, NVL(to_char(to_number(AT_CNTR_PCT),9999999999), ' ')AT_CNTR_PCT,
NVL(to_char(to_number(AT_CNTR_AMT),9999999999), ' ')AT_CNTR_AMT, NVL(to_char(to_number(PAYPD_SAL),9999999999), ' ')PAYPD_SAL,
NVL(PAYROLL_FRQ, ' ')PAYROLL_FRQ  
FROM (
SELECT *
FROM KSEND
)

GROUP BY GROUPING SETS ((DIVISION_ID), (PLAN_ID, DIVISION_ID, SSN, LAST_NAME, FIRST_NAME,
MI, DOB, GENDER, MARITAL_SC, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, DOH, DOT, REHIRE_DT, END_PR_DT, EE_DISCRET,
LOAN_PMT_2, YTD_HRS, YTD_TTL_CMP, YTD_PLN_CMP, PP_ENT_CMP, HCE_STAT, PCT_OWN, EXCL_CLASS, OFC_DETRM,
PTP_ELG_DT, ELIG_CLASS, BT_CNTR_PCT, BT_CNTR_AMT, AT_CNTR_PCT, AT_CNTR_AMT, PAYPD_SAL, PAYROLL_FRQ,
PR_ENDING_DATE))
ORDER BY DIVISION_ID, 1, LAST_NAME)
union all
select 'GRAND-TOTALS' PLAN_ID,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,SUM(EMP_DEFER) EMP_DEFER,
SUM(EMPR_MATCH) EMPR_MATCH,null,SUM(LOAN_PMT_1) LOAN_PMT_1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
from KSEND;

Open in new window

0
 

Author Comment

by:Glenn Stearns
ID: 33560946
That works!

Exactly what I needed it to do.
0
 

Author Closing Comment

by:Glenn Stearns
ID: 33560957
Thanks for bearing with me and teaching me how to do this kind of thing. I appreciate it!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question