Solved

PL/SQL Get Sub-Totals and Grand Total Row

Posted on 2010-08-30
8
2,428 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:glennes
  • 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:glennes
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:glennes
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:glennes
ID: 33560946
That works!

Exactly what I needed it to do.
0
 

Author Closing Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now