Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2017
    Posts
    8

    Unanswered: Nulls showing in Cube operator

    Hi,

    I am having issue with understanding Cube operator in SQL. I am using oracle 11g, Windows 10 x64

    I wrote this query which give out all the TOTAL SCORE and NUMBER OF ATTEMPTS by of each student :

    Code:
    Select S.Student_ID, S.Student_Name, S.Gender, S.Course, S.Nationality_Status, S.Tute_Grp, SUM(A.Score) as "TOTAL_SCORE", COUNT(T.Lab) as "TOTAL_ATTEMPT" from Student  S left  join Attempt A on S.Student_ID = A.Student_ID left  join Task T on T.Task_Code = A.Task_Code group by S.Student_ID, S.Student_Name, S.Gender, S.Course, S.Nationality_Status, S.tute_Grp order by S.Student_ID;
    The result to the above query:

    Code:
    STUDENT_ID | STUDENT_NAME   | GENDER | COURSE | NATIONALITY_STATUS | TUTE_GRP | TOTAL_SCORE | TOTAL_ATTEMPT
    1001       | GRANT NICHOLS  | MALE   | BH-ERM | LOCAL              | 1        | 33          | 19
    1002       | KELVIN NEWMAN  | MALE   | BA-ICT | LOCAL              | 4        | 60          | 26
    1003       | KELLY CUMMINGS | FEMALE | BA-ICT | LOCAL              | 4        | 42          | 21
    1004       | RICKEY ELLIS   | MALE   | BH-ERM | LOCAL              | 1        | 38          | 19
    1005       | CHELSEA ABBOTT | MALE   | BA-CS  | INTERNATIONAL      | 4        | 7           | 6
    1006       | HUBERT NGUYEN  | MALE   | BA-ICT | LOCAL              | 1        | 52          | 26
    1007       | BENJAMIN FORD  | MALE   | BA-ICT | LOCAL              | 3        | NULL        | 0
    The above query I saved as this view :

    Code:
    Create View  student_performance as Select S.Student_ID, S.Student_Name, S.Gender, S.Course, S.Nationality_Status, S.Tute_Grp, SUM(A.Score) as "TOTAL_SCORE", COUNT(T.Lab) as "TOTAL_ATTEMPT" from Student  S left  join Attempt A on S.Student_ID = A.Student_ID left  join Task T on T.Task_Code = A.Task_Code group by S.Student_ID, S.Student_Name, S.Gender, S.Course, S.Nationality_Status, S.tute_Grp order by S.Student_ID;
    Now I want to use the SQL command to find Male and Female Local and International students, so I wrote this :

    Code:
    Select NATIONALITY_STATUS, GENDER, COUNT(Gender) from student_performance group by cube(NATIONALITY_STATUS, GENDER);
    And I get this output, which shows nulls and I noticed that the Null Male is a total of Local Male and International Male and the same applies to other rows, while the NULL NULL 87 is the total number of students.

    My question how to not show Nulls and shows "TOTAL" of each instead of Null Text.

    Code:
    NATIONALITY_STATUS | GENDER | COUNT(GENDER)
    NULL | NULL | 87
    NULL | MALE | 74
    NULL | FEMALE | 13
    LOCAL | NULL | 67
    LOCAL | MALE | 55
    LOCAL | FEMALE | 12
    INTERNATIONAL | NULL | 20
    INTERNATIONAL | MALE | 19
    INTERNATIONAL | FEMALE | 1
    Last edited by TryllZ; 04-29-17 at 11:58.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,112
    Provided Answers: 5
    I want to use the SQL command to find Male and Female Local and International students
    Wouldn't that be a simple
    Code:
    select nationality_status, gender, count(*)
    from student_performance
    group by nationality_status, gender;

  3. #3
    Join Date
    Apr 2017
    Posts
    8
    Quote Originally Posted by Littlefoot View Post
    Wouldn't that be a simple
    Code:
    select nationality_status, gender, count(*)
    from student_performance
    group by nationality_status, gender;
    Sorry but I have to use the Cube Operator..

  4. #4
    Join Date
    Apr 2017
    Posts
    8
    Quote Originally Posted by TryllZ View Post
    Sorry but I have to use the Cube Operator..
    Thanks, I managed to remove null but the issue is it also removes the required data with it.

    Code:
    Select NATIONALITY_STATUS, GENDER, COUNT(*) as "TOTAL_STUDENTS" from student_performance group by cube(NATIONALITY_STATUS, GENDER) having gender is not null and nationality_status is not null;
    I wanted it to show the result in this way :

    Code:
    NATIONALITY_STATUS | GENDER | COUNT(GENDER)
    TOTAL STUDENTS (CURRENTLY SHOWING NULL) | MALE AND FEMALE (CURRENTLY SHOWING NULL) | 87
    TOTAL MALES (CURRENTLY SHOWING NULL) | MALE | 74
    TOTAL FEMALES (CURRENTLY SHOWING NULL) | FEMALE | 13
    LOCAL | TOTAL LOCALS (CURRENTLY SHOWING NULL) | 67
    LOCAL | MALE | 55
    LOCAL | FEMALE | 12
    INTERNATIONAL | TOTAL INTERNATIONAL STUDENTS (CURRENTLY SHOWING NULL) | 20
    INTERNATIONAL | MALE | 19
    INTERNATIONAL | FEMALE | 1

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,112
    Provided Answers: 5
    It seems that NVL is what you're looking for, such as
    Code:
    select 
      nvl(nationality_status, 'total ' || nvl(gender, 'students'))                    nationality_status, 
      nvl(gender, 'total '             || nvl(nationality_status, 'male and female')) gender, 
      count(*)
    from student_performance
    group by cube (nationality_status, gender) 
    order by 1, 2
    Adjust NVLs, if you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •