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:
