How to use grouping in oracle

The GROUPING function determines the nesting of the grouping level, returning 0 for the outermost grouping and 1 for the inner grouping. Can be used to identify the outermost grouping, calculate aggregate function results, and create conditions based on grouping.

GROUPING Function in Oracle

The GROUPING function is used to mark grouping levels in aggregate function calculations. It returns a value indicating whether the row belongs to the most deeply nested group.

Syntax

GROUPING(expr)

Where:

• expr is the column or expression that specifies the grouping range.

Usage

The GROUPING function can be used to:

• Identify rows in the outermost grouping.
• The result of the aggregate function is calculated only in the outermost grouping.
• Create conditions based on grouping hierarchy.

Return Value

The GROUPING function returns the following values:

• #0: If the row belongs to the most deeply nested grouping.
• 1: If the row belongs to an inner group.

Example

SELECT department_id, job_id, SUM(salary) AS total_salary,
       GROUPING(job_id) AS group_level
FROM employee
GROUP BY department_id, job_id;

Result:

• department_id is the outer group, and job_id is the inner group (group_level is 1).

The above is the detailed content of How to use grouping in oracle. For more information, please follow other related articles on the PHP Chinese website!