Solved

PL/SQL Get Sub-Totals and Grand Total Row

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
case statement in where clause with not exist 15 46
join 2 views with 5 conditions 3 46
SQL Query 34 82
Create table from select - oracle 6 24
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

895 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

11 Experts available now in Live!

Get 1:1 Help Now