Let Us Learn Oracle - Part 32 of N [ Cube function of Oracle ]

Purpose: It gives a cross tabulated summary of detail rows. It calculates subtotals for all possible combinations of group parameters.It also calculates the grand total.

Select DeptName,BelongsTo, Count(*) "Departmentwise Employee", Avg(Salary) "Deptwise Avg Sal"
From tblEmployee e
Join tblDept d 
Using(DeptId)
Group By Cube(DeptName,BelongsTo)
Order by Null desc;

/* Result */
DEPTNAME                                           BELONGSTO  Departmentwise Employee                Deptwise Avg Sal                       
Accounts                                           Singapore  6                                      3711.66666666667                       
Accounts                                                      6                                      3711.66666666667                       
Finance                                            Australia  4                                      5525                                   
Finance                                                       4                                      5525                                   
IT                                                 India      5                                      8202                                   
IT                                                            5                                      8202                                   
Sales                                              HongKong   1                                      650                                    
Sales                                                         1                                      650                                    
                                                   Australia  4                                      5525                                   
                                                   HongKong   1                                      650                                    
                                                   India      5                                      8202                                   
                                                   Singapore  6                                      3711.66666666667                       
                                                              16                                     5376.875                               
                   

We obtain the Countrywise summary too by using this function.

The same function is also available in Sql Server

Thanks for reading