Home » SQL & PL/SQL » SQL & PL/SQL » Query Totals with Rollup Question
Query Totals with Rollup Question [message #330160] Fri, 27 June 2008 14:05 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
I have the following query and I use the rollup to create totals for each group and a grand total.

Is there a way to create a grand total for the each Fin_Class so i can see the over all totals?


Select 
	sSite,
	Fin_Class, 
	Sum(Num_of_Accounts) As Num_of_Accounts, 
	Sum(Collection_Accounts) As Collection_Accounts,
	Sum(Num_of_Statements) Num_of_Statements,
	SUM(Total_Charges_Amount) Total_Charges_Amount,
	(SUM(Total_WO_Collections_Amount) * -1) Total_WO_Collections_Amount,
	GROUPING(sSite) AS gSite,
	GROUPING(Fin_Class) AS gFin_Class
from (
--------------------------------------------------------------
SELECT 
	ACCOUNT.CPCODE sSite, 
	ACCOUNT.BILLSTATUS Fin_Class,
	Count(ACCOUNT.CODE) Num_of_Accounts,
	SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
	sum(ACCOUNT.STATEMENTS) Num_of_Statements,
	0 Total_Charges_Amount,
	0 Total_WO_Collections_Amount
FROM   
	ACCOUNT ACCOUNT
WHERE 
	ACCOUNT.DATEIN between to_number(to_char({?Date_Begin},'j')) and to_number(to_char({?Date_End},'j'))
	AND (ACCOUNT.DEACTIVE IS  NULL)
	AND (ACCOUNT.EXCEPTION NOT IN ('UD','ED','04','20','21','22','23','24','25','26','27','28','29'))
GROUP BY
	ACCOUNT.CPCODE,ACCOUNT.BILLSTATUS
UNION ALL
SELECT 
	ACCOUNT.CPCODE sSite, 
	ACCOUNT.BILLSTATUS Fin_Class,
	0 Num_of_Accounts,
	0 Collection_Accounts,
	0 Num_of_Statements,
	SUM(CASE WHEN (CHARGES.TYPE='C') THEN CHARGES.PRAMOUNT ELSE 0 END) Total_Charges_Amount,
	SUM(CASE WHEN (CHARGES.TYPE='A') AND (CHARGES.PRCODE IN ('70','76','77','78')) THEN CHARGES.PRAMOUNT ELSE 0 END) Total_WO_Collections_Amount
FROM   
	ACCOUNT ACCOUNT
	JOIN CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND ((CHARGES.TYPE ='C') OR ((CHARGES.TYPE ='A') AND (CHARGES.PRCODE IN ('70','76','77','78')))) AND (CHARGES.SPLITFLAG IS  NULL))
WHERE 
	ACCOUNT.DATEIN between to_number(to_char({?Date_Begin},'j')) and to_number(to_char({?Date_End},'j'))
	AND (ACCOUNT.DEACTIVE IS  NULL)
	AND (ACCOUNT.EXCEPTION NOT IN ('UD','ED','04','20','21','22','23','24','25','26','27','28','29'))
GROUP BY
	ACCOUNT.CPCODE,ACCOUNT.BILLSTATUS
--------------------------------------------------------------
) U
Group By ROLLUP(sSite, Fin_Class)

Re: Query Totals with Rollup Question [message #330167 is a reply to message #330160] Fri, 27 June 2008 14:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
  1  select job, deptno, sal, case when job_grouping_id = 1 and grouping_deptno = 0
  2                                then 'SUM_JOB'
  3                                when job_grouping_id = 0 and grouping_deptno = 1
  4                                then 'SUMMARY_DEPT'
  5                                when job_grouping_id = 1 and grouping_deptno = 1
  6                                then 'GRAND_TOTAL'
  7                                else 'DETAIL'
  8                            end grouping_text
  9  from
 10  (
 11      select sum(sal) sal, job, deptno, grouping_id(job) job_grouping_id, grouping_id(deptno) grouping_deptno from emp
 12      group by rollup(job), rollup(deptno), ()
 13  )
 14* order by job_grouping_id, grouping_deptno
SQL> /

JOB           DEPTNO        SAL GROUPING_TEX
--------- ---------- ---------- ------------
PRESIDENT         10       5000 DETAIL
SALESMAN          30       5600 DETAIL
MANAGER           30       2850 DETAIL
CLERK             30        950 DETAIL
MANAGER           20       2975 DETAIL
ANALYST           20       6000 DETAIL
CLERK             20       1900 DETAIL
MANAGER           10       2450 DETAIL
CLERK             10       1300 DETAIL
PRESIDENT                  5000 SUMMARY_DEPT
ANALYST                    6000 SUMMARY_DEPT
MANAGER                    8275 SUMMARY_DEPT
SALESMAN                   5600 SUMMARY_DEPT
CLERK                      4150 SUMMARY_DEPT
                  10       8750 SUM_JOB
                  20      10875 SUM_JOB
                  30       9400 SUM_JOB
                          29025 GRAND_TOTAL

18 rows selected.

Hope this helps.

Regards

Raj
Re: Query Totals with Rollup Question [message #330168 is a reply to message #330160] Fri, 27 June 2008 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CUBE instead of ROLLUP.

Regards
Michel
Re: Query Totals with Rollup Question [message #330528 is a reply to message #330168] Mon, 30 June 2008 06:53 Go to previous message
fmrock
Messages: 45
Registered: December 2006
Member
Perfect. Thanks guys!
Previous Topic: union
Next Topic: CONNECT BY in a graph
Goto Forum:
  


Current Time: Sat Dec 10 18:46:53 CST 2016

Total time taken to generate the page: 0.09483 seconds