Solved

PL/SQL Get Sub-Totals and Grand Total Row

Posted on 2010-08-30
8
2,648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 500 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

Industry Leaders: 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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

627 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