This discussion is archived
2 Replies Latest reply: Apr 26, 2013 2:14 PM by Roxyrollers RSS

A Matrix Output for some enthusiastic students

Roxyrollers Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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
  • 2. Re: A Matrix Output for some enthusiastic students
    Roxyrollers Newbie
    Currently Being Moderated
    Thanks a bunch!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points