group by cube : Cube « Analytical Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Analytical Functions
3. Cube

group by cube

 



SQL> create table student (
  2  student_id  number primary key,
  3  student_name  varchar2(25),
  4  student_year  varchar2(15),
  5  student_major  varchar2(25) );

Table created.

SQL>
SQL> create table class (
  2  class_id    number primary key,
  3  class_desc  varchar2(35),
  4  credit_hrs  number(2) );

Table created.

SQL>
SQL> create table grades (
  2  student_id  number,
  3  class_id    number,
  4  assignment_desc varchar2(200),
  5  grade_received  number(3) );

Table created.

SQL>
SQL> insert into student values (1,'Tom','First','Art');

1 row created.

SQL> insert into student values (2,'Jack','First','Med');

1 row created.

SQL> insert into student values (3,'Peter','First','History');

1 row created.

SQL> insert into student values (4,'Jason','First','Science');

1 row created.

SQL> insert into student values (5,'Joe','Second','Education');

1 row created.

SQL> insert into student values (6,'Cat','Second','Finance');

1 row created.

SQL> insert into student values (7,'Sill','Second','Art');

1 row created.

SQL> insert into student values (8,'Bill','Second','Med');

1 row created.

SQL> insert into student values (9,'Mary','Third','History');

1 row created.

SQL>
SQL> insert into class values (1,'Public Speaking 101',3);

1 row created.

SQL> insert into class values (2,'English 101',3);

1 row created.

SQL> insert into class values (3,'English 201',3);

1 row created.

SQL> insert into class values (4,'English 301',3);

1 row created.

SQL> insert into class values (5,'English 401',3);

1 row created.

SQL> insert into class values (6,'Marketing 101',3);

1 row created.

SQL> insert into class values (7,'Child Development 101',3);

1 row created.

SQL> insert into class values (8,'Golf for Novices',2);

1 row created.

SQL> insert into class values (9,'Biology 101',4);

1 row created.

SQL>
SQL> insert into grades values (1,1,'Exam 1',94);

1 row created.

SQL> insert into grades values (7,1,'Exam 1',88);

1 row created.

SQL> insert into grades values (4,1,'Exam 1',85);

1 row created.

SQL> insert into grades values (1,1,'Exam 2',87);

1 row created.

SQL> insert into grades values (7,1,'Exam 2',89);

1 row created.

SQL> insert into grades values (4,1,'Exam 2',91);

1 row created.

SQL> insert into grades values (1,1,'Paper 1',90);

1 row created.

SQL> insert into grades values (7,1,'Paper 1',82);

1 row created.

SQL>
SQL>  select c.class_desc as class, s.student_name as student, avg(g.grade_received) as grade_avg,
  2          grouping(c.class_desc) as class_ind, grouping(s.student_name) as stud_ind
  3     from class c, student s, grades g
  4    where c.class_id = g.class_id
  5      and s.student_id = g.student_id
  6    group by cube (c.class_desc, s.student_name) ;

CLASS                     STUDENT                        GRADE_AVG  CLASS_IND   STUD_IND
------------------------- ------------------------------ --------- ---------- ----------
                                                             88.25          1          1
                          Tom                                90.33          1          0
                          Sill                               86.33          1          0
                          Jason                              88.00          1          0
Public Speaking 101                                          88.25          0          1
Public Speaking 101       Tom                                90.33          0          0
Public Speaking 101       Sill                               86.33          0          0
Public Speaking 101       Jason                              88.00          0          0

8 rows selected.

SQL>
SQL>
SQL> drop table class;

Table dropped.

SQL> drop table student;

Table dropped.

SQL> drop table grades;

Table dropped.

   
  

Related examples in the same category