2 Replies Latest reply: Apr 26, 2013 4:14 PM by Roxyrollers RSS

    A Matrix Output for some enthusiastic students

    Roxyrollers
      Hi folks,
      Greetings. I am trying to create a Matrix Output for some Students who have decided to take Music and/or Sports or neither. I am hoping to break it down by Age and Gender and Interest (Music/Sports).

      Any help is greatly appreciated!

      Thanks

      Scripts to create the tables and INSERT records
      create table student_tb(student_id varchar2(4), birth_date date, gender varchar2(1));
      create table sports_tb (student_id varchar2(4));
      create table music_tb  (student_id varchar2(4));
      
      -- INSERTing into STUDENT_TB
      insert into student_tb (student_id,birth_date,gender) values ('1001',to_date('01-JAN-90','DD-MON-RR'),'M');
      insert into student_tb (student_id,birth_date,gender) values ('1002',to_date('10-JUL-91','DD-MON-RR'),'M');
      insert into student_tb (student_id,birth_date,gender) values ('1003',to_date('01-DEC-90','DD-MON-RR'),'M');
      insert into student_tb (student_id,birth_date,gender) values ('1004',to_date('10-AUG-92','DD-MON-RR'),'F');
      insert into student_tb (student_id,birth_date,gender) values ('1005',to_date('01-JAN-91','DD-MON-RR'),'F');
      insert into student_tb (student_id,birth_date,gender) values ('1006',to_date('01-FEB-91','DD-MON-RR'),'F');
      insert into student_tb (student_id,birth_date,gender) values ('1007',to_date('01-AUG-90','DD-MON-RR'),'M');
      insert into student_tb (student_id,birth_date,gender) values ('1008',to_date('01-SEP-90','DD-MON-RR'),'F');
      insert into student_tb (student_id,birth_date,gender) values ('1009',to_date('01-OCT-90','DD-MON-RR'),'F');
      insert into student_tb (student_id,birth_date,gender) values ('1010',to_date('10-MAR-89','DD-MON-RR'),'F');
      
      -- INSERTing into SPORTS_TB
      insert into sports_tb (student_id) values ('1001');
      insert into sports_tb (student_id) values ('1004');
      insert into sports_tb (student_id) values ('1005');
      insert into sports_tb (student_id) values ('1009');
      insert into sports_tb (student_id) values ('1010');
      
      
      -- INSERTing into MUSIC_TB
      insert into music_tb (student_id) values ('1001');
      insert into music_tb (student_id) values ('1003');
      insert into music_tb (student_id) values ('1010');
      I am hoping to have an Output in this fashion
      Please note that I am not intending on adding up the Males and Females on a row by row basis. That's because its the same Student who could be occuring in each Category (MUSIC/SPORTS)
      --------- MUSIC -------------------- SPORTS -----------
           Females      Males         Females        Males
      Age            
      20    0             0              1             0
      21    0             0              0             0
      22    0             1              2             0
      23    0             1              0             1
      24    1             0              1             0
      My Query - Problem: I clearly have all the data in here but I have lost the information where how many girls or boys are taking Sports, Music or Neither
      select age,
             sum(female) female_count,
             sum(male)   male_count,
             count(music_student_id) music_count,
             count(sport_student_id) sports_count
      from
      (
        select age, male, female, students.student_id student_id, music.student_id music_student_id, sports.student_id sport_student_id
        from
        (
          select student_id, trunc(months_between(sysdate, birth_date)/12) age,
                 (
                   case gender
                     when 'F' then 1
                     else 0
                   end
                 ) female,
                 (
                   case gender
                     when 'M' then 1
                     else 0
                   end
                 ) male
          from   student_tb
        ) students,
        (
          select student_id from music_tb
        ) music,
        (
          select student_id from sports_tb
        ) sports
        where students.student_id = music.student_id(+)
        and   students.student_id = sports.student_id(+)
      )
      group by age
      order by age;
        • 1. Re: A Matrix Output for some enthusiastic students
          Alex Fatkulin
          SQL> select age,
            2            sum(case when gender='F' and mid is not null then 1 else 0 end) music_females,
            3            sum(case when gender='M' and mid is not null then 1 else 0 end) music_males,
            4            sum(case when gender='F' and sid is not null then 1 else 0 end) sport_females,
            5            sum(case when gender='M' and sid is not null then 1 else 0 end) sport_males
            6  from (
            7  select trunc(months_between(sysdate, a.birth_date)/12) age, a.gender, s.student_id sid, m.student_id mid
            8       from student_tb a, sports_tb s, music_tb m
            9       where a.student_id = s.student_id (+)
           10            and a.student_id = m.student_id (+)
           11  ) group by age
           12  order by age;
           
                 AGE MUSIC_FEMALES MUSIC_MALES SPORT_FEMALES SPORT_MALES
          ---------- ------------- ----------- ------------- -----------
                  20             0           0             1           0
                  21             0           0             0           0
                  22             0           1             2           0
                  23             0           1             0           1
                  24             1           0             1           0