Lesson 29 : ROLLUP option in SELECT

ROLLUP in SELECT Queries  

ROLLUP enables a SELECT statement to compute multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause.

It creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns.  For example, if a query specifies ROLLUP on grouping columns of dept,location, and  salar (n=3), the result set will include rows at four aggregation levels.

Syntax :

SELECT col1, group function(col2), .. FROM <tablename> GROUP  BY  ROLLUP(column list)

Examples :

SQL> create table employee(name varchar(10),dept_id number(3),location varchar(10),salary number(5));

Table created.

SQL> insert into employee values('John',10,'Dallas',50000);

1 row created.

SQL> insert into employee values('Devy',10,'Dallas',75000);

1 row created.

SQL> insert into employee values('Paul',20,'Texas',85000);

 
1 row created.

SQL> insert into employee values('Raj',20,'Texas',65000);

1 row created.

SQL> insert into employee values('Chetan',30,'Boston',95000);

1 row created.

SQL> insert into employee values('Jayne',30,'Boston',55000);

1 row created.

SQL> insert into employee values('Senthil',30,'Chennai',45000);

1 row created.

SQL> insert into employee values('Jeya',30,'Chennai',25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

---------- ---------- ---------- ----------
John               10 Dallas          50000
Devy               10 Dallas          75000
Paul               20 Texas           85000
Raj                20 Texas           65000
Chetan             30 Boston          95000
Jayne              30 Boston          55000
Senthil            30 Chennai         45000
Jeya               30 Chennai         25000

8 rows selected.

SQL> select location,dept_id,count(*),sum(salary) from employee group by (location,dept_id);

LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)
---------- ---------- ---------- -----------
Texas              20          2      150000
Boston           30          2      150000
Dallas             10          2      125000
Chennai         30          2       70000

SQL> select location,dept_id,count(*),sum(salary) from employee group by rollup(location,dept_id);

 LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)

---------- ---------- ---------- -----------
Texas              20          2      150000
Texas                            2      150000
Boston           30          2      150000
Boston                         2      150000
Dallas             10          2      125000
Dallas                          2      125000
Chennai         30          2       70000
Chennai                        2       70000
                                     8      495000

 9 rows selected.

 
SQL> select location,dept_id,sum(salary) from employee group by rollup(location,dept_id);

LOCATION      DEPT_ID SUM(SALARY)

---------- ---------- -----------
Texas              20      150000
Texas                      150000
Boston             30      150000
Boston                     150000
Dallas             10      125000
Dallas                     125000
Chennai            30       70000
Chennai                     70000
                                495000
9 rows selected.

SQL>