43. Which statement best describes the GROUPING function?

A. It is used to set the order for the groups to be used for calculating the

grand totals and subtotals.

B. It is used to form. various groups to calculate total and subtotals created

using ROLLUP and CUBE operators.

C. It is used to identify if the NULL value in an expression is a stored NULL

value or created by ROLLUP or CUBE.

D. It is used to specify the concatenated group expressions to be used for

calculating the grand totals and subtotals.

Answer: C

The GROUPING function identifies superaggregate or aggregate rows produced by

a ROLLUP or CUBE operation in a SELECT . . . GROUP BY statement. It returns a value

of the NUMBER datatype, and its value is either a one (1) or a zero (0).The GROUPING

function is only valid in a SELECT statement that uses a GROUP BY clause. While GROUPING

may be used in a GROUP BY that doesn’t include the ROLLUP or CUBE operation, it doesn’t

produce anything meaningful without those operators—it will always return a zero if

ROLLUP and CUBE are absent from the statement.

SQL> select grouping(division_id),division_id,sum(salary)

from employees2

group by rollup(division_id)

order by division_id;

GROUPING(DIVISION_ID) DIV SUM(SALARY)

--------------------- --- -----------

0 BUS 1610000

0 OPE 1320000

0 SAL 4936000

0 SUP 1015000

1 8881000

可以看到，为空的地方返回1，非空的地方返回0。

ITPub博客

047-043