having grouping(c.class_desc) = 1 : GROUPING « Analytical Functions « Oracle PL / SQL






having grouping(c.class_desc) = 1

 
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> insert into grades values (4,1,'Paper 1',89);

1 row created.

SQL>
SQL> col class noprint
SQL> col class_ind noprint
SQL> col stud_ind noprint
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)
  7   having grouping(c.class_desc) = 1 ;

STUDENT                    GRADE_AVG
------------------------- ----------
Jason                     88.3333333
Sill                      86.3333333
Tom                       90.3333333
                          88.3333333

4 rows selected.

SQL>
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

1.GROUPING() returns 0 for the rows that have non-null values, and 1 for the last row that has a null value
2.GROUPING(x): either zero or one
3.Using DECODE() to Convert the Returned Value from GROUPING()
4.Using DECODE() and GROUPING() to Convert Multiple Column Values
5.Using GROUPING() with CUBE
6.Demonstrate GROUPING in the HAVING clause