Oracle SQL Query - Rollup Analytical Function

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle FAQ


	Don Burleson

Oracle SQL Query - Rollup Analytical Function

Is it advisable to make a single query to get the number of male, female, child patients in a given department (more than one) or make single query for each (no. of male, female, child) for a given department? I have a single query to get the record by using inline query. The query takes a long time if the number of departments is large.

Answer:

Try the ROLLUP analytical function. ROLLUP enables a SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. It also only does one-pass through the table, so it's efficient too!

ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use.

SELECT

deptno,

job,

count(*),

sum(sal)
FROM

emp
GROUP BY

ROLLUP(deptno,job);

   DEPTNO JOB         COUNT(*)   SUM(SAL)
--------- --------- ---------   ---------
       10 CLERK              1       1300
       10 MANAGER            1       2450
       10 PRESIDENT          1     5000
-->   10                    3       8750
       20 ANALYST            2       6000
       20 CLERK              2       1900
       20 MANAGER            1       2975
-->   20                    5      10875

Also, see these notes on using rollup by cube:

Oracle Analytical SQL functions - rollup - cube

Oracle Using the SQL GROUPING function

If you like Oracle tuning, you may enjoy my bestselling book Oracle Tuning: The Definitive Reference", with over 900 pages of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

��