ROLLUP

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.

For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.

Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.

The following query uses rollup operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales
     group by rollup(prod,year);

CUBE

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.

For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.

The following query uses CUBE operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales
     group by CUBE(prod,year);

CASE EXPRESSION

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

For example the following query uses CASE expression to display Department Names based on deptno.

select empno,ename,sal,CASE deptno when 10 then  
  ‘Accounts’ when 20 then ‘Sales’
              when 30 then ‘R&D’
               else “Unknown’ end
                   from emp;

The following statement finds the average salary of the employees in the employees table using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal
   ELSE 2000 END) "Average Salary" from emp e;