Les 04

Generating Reports by Grouping
Related Data
Copyright 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do

the following:
Use the ROLLUP operation to produce
subtotal values
Use the CUBE operation to produce crosstabulation values
Use the GROUPING function to identify the row
values created by ROLLUP or CUBE
Use GROUPING SETS to produce a single result set
4-2
Review of Group Functions
Group functions operate on sets of rows to give

one result per group.
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
[column,] group_function(column). . .
table
condition]
group_by_expression]
column];
Example:
SELECT AVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM
employees
WHERE job_id LIKE 'SA%';
4-3
Review of the GROUP BY Clause
Syntax:
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
table
condition]
column];
Example:
SELECT
department_id, job_id, SUM(salary),

COUNT(employee_id)
FROM
employees
GROUP BY department_id, job_id ;
4-4
Review of the HAVING Clause
Use the HAVING clause to specify which groups

are to be displayed.
You further restrict the groups on the basis of a
limiting condition.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
4-5
[column,] group_function(column)...
table
condition]
having_expression]
column];
GROUP BY with ROLLUP and

CUBE Operators
4-6
Use ROLLUP or CUBE with GROUP BY to produce

superaggregate rows by cross-referencing
columns.
ROLLUP grouping produces a result set containing
the regular grouped rows and the subtotal values.
CUBE grouping produces a result set containing
the rows from ROLLUP and cross-tabulation rows.
ROLLUP Operator
ROLLUP is an extension to the GROUP BY clause.

Use the ROLLUP operation to produce cumulative
aggregates, such as subtotals.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
4-7
table
condition]
[ROLLUP] group_by_expression]
having_expression];
column];
ROLLUP Operator: Example

SELECT
FROM
WHERE
GROUP BY
department_id, job_id, SUM(salary)

employees
department_id < 60
ROLLUP(department_id, job_id);
2
3
4-8
CUBE Operator
CUBE is an extension to the GROUP BY clause.

You can use the CUBE operator to produce crosstabulation values with a single SELECT statement.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
4-9
[column,] group_function(column)...
table
condition]
[CUBE] group_by_expression]
having_expression]
column];
CUBE Operator: Example

SELECT
FROM
WHERE
GROUP BY
department_id, job_id, SUM(salary)

employees
department_id < 60
CUBE (department_id, job_id) ;
1
2
3
4
4-10
GROUPING Function
The GROUPING function:

Is used with either the CUBE or ROLLUP operator
Is used to find the groups forming the subtotal in
a row
Is used to differentiate stored NULL values from
NULL values created by ROLLUP or CUBE
Returns 0 or 1
SELECT
[column,] group_function(column) .. ,
GROUPING(expr)
FROM
table
[WHERE
condition]
[GROUP BY [ROLLUP][CUBE] group_by_expression]
[HAVING
having_expression]
[ORDER BY column];
4-11
GROUPING Function: Example

SELECT
department_id DEPTID, job_id JOB,

SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM
employees
WHERE
department_id < 50
GROUP BY ROLLUP(department_id, job_id);
3
4-12
GROUPING SETS
GROUPING SETS syntax is used to define multiple

groupings in the same query.
All groupings specified in the GROUPING SETS
clause are computed and the results of individual
groupings are combined with a UNION ALL
operation.
Grouping set efficiency:
Only one pass over the base table is required.
There is no need to write complex UNION
statements.
The more elements GROUPING SETS has, the greater
the performance benefit.
4-13
GROUPING SETS: Example

SELECT
department_id, job_id,
manager_id,avg(salary)
FROM
employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id));
4-15
Composite Columns
A composite column is a collection of columns

that are treated as a unit.
ROLLUP (a, (b,c) , d)
4-17
Use parentheses within the GROUP BY clause to

group columns, so that they are treated as a unit
while computing ROLLUP or CUBE operations.
When used with ROLLUP or CUBE, composite
columns would require skipping aggregation
across certain levels.
Composite Columns: Example

SELECT
department_id, job_id, manager_id,

SUM(salary)
FROM
employees
GROUP BY ROLLUP( department_id,(job_id, manager_id));
1
2
3
4
4-19
Concatenated Groupings
Concatenated groupings offer a concise way to

generate useful combinations of groupings.
To specify concatenated grouping sets, you
separate multiple grouping sets, ROLLUP, and
CUBE operations with commas so that the Oracle
server combines them into a single GROUP BY
clause.
The result is a cross-product of groupings from
each grouping set.
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
4-21
Concatenated Groupings: Example

SELECT
department_id, job_id, manager_id,

SUM(salary)
FROM
employees
GROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);
1
2
3
4-22
Summary
In this lesson, you should have learned how to use

the:
ROLLUP operation to produce subtotal values
CUBE operation to produce cross-tabulation values
GROUPING function to identify the row values
created by ROLLUP or CUBE
GROUPING SETS syntax to define multiple
groupings in the same query
GROUP BY clause to combine expressions in
various ways:
Composite columns
Concatenated grouping sets
4-23
Practice 4: Overview
This practice covers using:

ROLLUP operators
CUBE operators
GROUPING functions
GROUPING SETS
4-24

Les 04

Uploaded by

Copyright:

Available Formats

Les 04

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Les 04

Uploaded by

Copyright:

Available Formats

Generating Reports by Grouping

Copyright 2004, Oracle. All rights reserved.

After completing this lesson, you should be able to do

Copyright 2004, Oracle. All rights reserved.

Review of Group Functions

Group functions operate on sets of rows to give

Copyright 2004, Oracle. All rights reserved.

Review of the GROUP BY Clause

department_id, job_id, SUM(salary),

Copyright 2004, Oracle. All rights reserved.

Review of the HAVING Clause

Use the HAVING clause to specify which groups

Copyright 2004, Oracle. All rights reserved.

GROUP BY with ROLLUP and

Use ROLLUP or CUBE with GROUP BY to produce

Copyright 2004, Oracle. All rights reserved.

ROLLUP is an extension to the GROUP BY clause.

Copyright 2004, Oracle. All rights reserved.

ROLLUP Operator: Example

department_id, job_id, SUM(salary)

Copyright 2004, Oracle. All rights reserved.

CUBE is an extension to the GROUP BY clause.

Copyright 2004, Oracle. All rights reserved.

CUBE Operator: Example

department_id, job_id, SUM(salary)

Copyright 2004, Oracle. All rights reserved.

The GROUPING function:

Copyright 2004, Oracle. All rights reserved.

GROUPING Function: Example

department_id DEPTID, job_id JOB,

Copyright 2004, Oracle. All rights reserved.

GROUPING SETS syntax is used to define multiple

Copyright 2004, Oracle. All rights reserved.

GROUPING SETS: Example

Copyright 2004, Oracle. All rights reserved.

A composite column is a collection of columns

Use parentheses within the GROUP BY clause to

Copyright 2004, Oracle. All rights reserved.

Composite Columns: Example

department_id, job_id, manager_id,

Copyright 2004, Oracle. All rights reserved.

Concatenated groupings offer a concise way to

Copyright 2004, Oracle. All rights reserved.

Concatenated Groupings: Example

department_id, job_id, manager_id,

Copyright 2004, Oracle. All rights reserved.

In this lesson, you should have learned how to use

Copyright 2004, Oracle. All rights reserved.

This practice covers using:

Copyright 2004, Oracle. All rights reserved.

You might also like