Solved

PL/SQL Get Sub-Totals and Grand Total Row

Posted on 2010-08-30
8
2,517 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
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.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

829 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