New SQL Keywords for Computing Totals

New SQL Keywords for Computing Totals

ROLLUP – addition to the GROUP BY clause. Will return a single superaggregate row summary line for each group specified. It is considered an aggregate operator. Can produce subtotals and a grand total.
Usage is:
GROUP BY ROLLUP (f1...fn), where f is a field or fields referenced in the SELECT portion of a SQL statement.
Example:
SELECT empno,
COUNT(*) "emp count"
FROM emp
GROUP BY ROLLUP (empno);
This example will list the count of rows in this table for each employee and a extra single line that will contain a “rolled up” summary value of the count(*) field.
CUBE – addition to the GROUP BY clause. Will return a row summary line for each combination of groups specified. Use to create values for a cross-tabulation type of report. Is considered an aggregate operator. These aggregate operators are more efficient than regularly written SQL statements that create the same result because these aggregate functions use hints.
Usage is:
GROUP BY CUBE (f1...fn), where f is a field or fields referenced in the SELECT portion of a SQL statement.
Example:
SELECT deptno,
job,
COUNT(*) "emp count",
AVG(sal) "average salary"
FROM emp
GROUP BY CUBE (deptno, job);
This example returns the employee count and avg salary for each job within each department. The CUBE option also produces a summary line with the employee count and avg salary for in each dept across all jobs, a summary line for each job across all departments and a single line with values summed over all depts and jobs.
GROUPING function – returns a 0 (zero) if a NULL value represents an actual NULL. Will return a 1 (one) if a NULL value is the result of the ROLLUP or CUBE aggregate functions.