E Computer Notes - Enhancements To The GROUP by Clause

Enhancements to the GROUP BY Clause
http://ecomputernotes.com
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
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%';
Review of the GROUP BY Clause

Syntax:
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
[column,] group_function(column). . . table condition] group_by_expression] column];
Example:
SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ;
Review of the HAVING Clause

SELECT
FROM
[WHERE
[GROUP BY [HAVING
[ORDER BY
[column,] group_function(column)... table condition] group_by_expression] having_expression] column];
Use the HAVING clause to specify which groups are to be displayed. You further restrict the groups on the basis of a limiting condition.
GROUP BY with ROLLUP and CUBE Operators
Use ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns. ROLLUP grouping produces a results set containing the regular grouped rows and the subtotal values. CUBE grouping produces a results set containing the rows from ROLLUP and cross-tabulation rows.
ROLLUP Operator
SELECT
FROM
[WHERE
[GROUP BY [HAVING
[ORDER BY
[column,] group_function(column). . . table condition] [ROLLUP] group_by_expression] having_expression]; column];
ROLLUP is an extension to the GROUP BY clause.
Use the ROLLUP operation to produce cumulative aggregates, such as subtotals.
ROLLUP Operator Example

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id);
CUBE Operator
SELECT
FROM
[WHERE
[GROUP BY [HAVING
[ORDER BY
[column,] group_function(column)... table condition] [CUBE] group_by_expression] having_expression] column];
CUBE is an extension to the GROUP BY clause.
You can use the CUBE operator to produce crosstabulation values with a single SELECT statement.
CUBE Operator: Example

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ;
1 2
3 4
GROUPING Function
SELECT [column,] group_function(column) . , GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];
The GROUPING function can be used with either the CUBE or ROLLUP operator. groups forming the subtotal in a row. Using the GROUPING function, you can differentiate stored NULL values from NULL values created by ROLLUP or CUBE. The GROUPING function returns 0 or 1.
Using the GROUPING function, you can find the
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); 1
2 3
GROUPING SETS

GROUPING SETS are a further extension of the GROUP BY clause. You can use GROUPING SETS to define multiple groupings in the same query. The Oracle Server computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings 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 the GROUPING SETS have, the greater the performance benefit.
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));
Composite Columns
A composite column is a collection of columns that are treated as a unit.
ROLLUP (a,
To specify composite columns, use the GROUP BY clause to group columns within parentheses so that the Oracle server treats them as a unit while computing ROLLUP or CUBE operations. When used with ROLLUP or CUBE, composite columns would mean skipping aggregation across certain levels.
(b,c)
, d)
Composite Columns: Example
SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));
Concatenated Groupings
Concatenated groupings offer a concise way to generate useful combinations of groupings. 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.
To specify concatenated grouping sets, you
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
Concatenated Groupigs 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

E Computer Notes - Enhancements To The GROUP by Clause

Uploaded by

Copyright:

Available Formats

E Computer Notes - Enhancements To The GROUP by Clause

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

E Computer Notes - Enhancements To The GROUP by Clause

Uploaded by

Copyright:

Available Formats

Enhancements to the GROUP BY Clause

Review of Group Functions

[column,] group_function(column). . . table condition] group_by_expression] column];

Review of the GROUP BY Clause

[column,] group_function(column). . . table condition] group_by_expression] column];

Review of the HAVING Clause

[column,] group_function(column)... table condition] group_by_expression] having_expression] column];

GROUP BY with ROLLUP and CUBE Operators

[column,] group_function(column). . . table condition] [ROLLUP] group_by_expression] having_expression]; column];

ROLLUP is an extension to the GROUP BY clause.

Use the ROLLUP operation to produce cumulative aggregates, such as subtotals.

ROLLUP Operator Example

[column,] group_function(column)... table condition] [CUBE] group_by_expression] having_expression] column];

CUBE is an extension to the GROUP BY clause.

CUBE Operator: Example

Using the GROUPING function, you can find the

GROUPING Function: Example

GROUPING SETS: Example

Composite Columns: Example

To specify concatenated grouping sets, you

GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)

Concatenated Groupigs Example

You might also like