Powerful OLAP Functions Introduced
in Oracle9i Onwards
Some of the OLAP Function Introduced in the Oracle9i Onwards
are Very handy when it comes to do aggregations at
Different Levels .
For example Rollup Function
can be Used forGetting theSubtotals and Grand totals in Just a Single Query
Instead of
Using Many Sub queries.
Using the Rollup FunctionFor
Getting the Subtotals andGrand totals .
This place I am Just givinga
Demo of Using the Rollup and the Grouping Functions.
SELECTT.STRSTMPAPTYPE ,
COUNT(T.STRSTMSTDID)FROM TBLSTMTOT T
GROUP BY ROLLUP(T.STRSTMPAPTYPE)
CREATE PUBLIC SYNONYM REG
FOR TBLSATSRGSTDREG
SELECT T.INTSTGGROUPID
, T.STRSTGCLASS ,COUNT(T.STRSRGSTDID)
FROM REG T
GROUP BY ROLLUP(T.INTSTGGROUPID
, T.STRSTGCLASS)
SELECT T.INTSTGGROUPID
, T.STRSTGCLASS ,COUNT(T.STRSRGSTDID),
GROUPING(T.INTSTGGROUPID)
AS "1",
GROUPING(T.STRSTGCLASS)
AS "2"
FROM REG T
GROUP BY ROLLUP(T.INTSTGGROUPID
, T.STRSTGCLASS)
SELECT T.INTSTGGROUPID
,dECODE(GROUPING(T.INTSTGGROUPID)+GROUPING(T.STRSTGCLASS),1,'SUBTOTAL'
,
2, 'GRAND TOTAL',T.STRSTGCLASS)
,
COUNT(T.STRSRGSTDID),
GROUPING(T.INTSTGGROUPID) AS "1",
GROUPING(T.STRSTGCLASS)
AS "2"
FROM REG T
GROUP BY ROLLUP(T.INTSTGGROUPID
, T.STRSTGCLASS)
SELECT T.INTSTGGROUPID
,
DECODE(GROUPING(T.INTSTGGROUPID)+GROUPING(T.STRSTGCLASS),1,'TOTAL
OF SCHOOL' ,
2, 'GRAND TOTAL',T.STRSTGCLASS)
AS "CLASS" ,
COUNT(T.STRSRGSTDID) "STUDENT
COUNT"
FROM REG T
WHERE T.INTSTGGROUPIDIN
(67,99)
GROUP BY ROLLUP(T.INTSTGGROUPID
, T.STRSTGCLASS)
SELECT T.INTSTGGROUPID
,
DECODE(GROUPING(T.INTSTGGROUPID)+GROUPING(T.STRSTGCLASS),1,'TOTAL
OF SCHOOL' ,
2, 'GRAND TOTAL',T.STRSTGCLASS)
AS "CLASS" ,
COUNT(T.STRSRGSTDID) "STUDENT
COUNT"
FROM REG T
GROUP BY ROLLUP(T.INTSTGGROUPID
, T.STRSTGCLASS)
having GROUPING(T.INTSTGGROUPID)+GROUPING(T.STRSTGCLASS)
>0
As a note, it is possible to calculate the same results
using only SQL Select, without the ROLLUP clause and without
programming.
This uses several SELECTs joined by UNION ALL.These tend
to be complicated to write and less efficient to execute.
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle
Certification, Database Administration, SQL, Application, Programming Reference
Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|