1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

help me get a count students taking classes (; separated values)..thanks

Discussion in 'SQL PL/SQL' started by sandy5, Apr 8, 2011.

  1. sandy5

    sandy5 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    Can some one please help me write an oracle query to get the # of
    students taking a particular class.

    I am using Oracel 9i

    I have a table with Student_id, classes, teacher


    and the values look like


    111 Math;Social;Science Ms.Reed
    112 Math;Science;PE Mr.Smith
    113 PE;Social;Math Ms.Reed
    114 Art;PE;Reading Mr.Jones


    I want the result to display as
    1.Classname and studentcount

    Math 3
    Science 2
    Social 2
    Art 1
    PE 2
    Reading 1


    2. Classname studentcount groupedby teacher name

    Math 2 Ms.Reed
    Math 1 Mr.Smith
    Social 2 Ms.Reed
    Science 1 Mr.Smith
    Science 1 Ms.Reed
    PE 1 Mr.Smith
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this homework assignment? Please post your code and someone can possibly assist you.
     
  3. sandy5

    sandy5 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I am newbie learning oracle, I have no clue on how to proceed with this.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Are you taking a class or learning this on your own?
     
  5. sandy5

    sandy5 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Learning this on my own as I started working in a firm as a intern student.
    Main developer is working on this for couple days, so I thought I should also give it a try, so I changed the field names and sent a request to you (while trying it out on my own).
    Thanks for all your help
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With your data you really cannot use plain SQL to do this (at least I could not as you have restricted yourself to 9i); PL/SQL can do this:

    Code (SQL):
     
    SQL> CREATE TABLE students(
      2          stud_id NUMBER NOT NULL,
      3          classlist varchar2(80),
      4          instructor varchar2(50));
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO students
      3  VALUES(111,'Math;Social;Science','Ms.Reed')
      4  INTO students
      5  VALUES(112,'Math;Science;PE','Mr.Smith')
      6  INTO students
      7  VALUES(113,'PE;Social;Math','Ms.Reed')
      8  INTO students
      9  VALUES(114,'Art;PE;Reading','Mr.Jones')
     10  SELECT * FROM dual;
     
    4 ROWS created.
     
    SQL>

    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> DECLARE
      2          cursor getstud_data IS
      3          SELECT classlist
      4          FROM students;
      5
      6          mathct NUMBER:=0;
      7          pect NUMBER:=0;
      8          socialct NUMBER:=0;
      9          sciencect NUMBER:=0;
     10          readct NUMBER:=0;
     11          artct NUMBER:=0;
     12  BEGIN
     13
     14          FOR clsrec IN getstud_data loop
     15                  IF instr(clsrec.classlist, 'Math') > 0 THEN
     16                          mathct:=mathct + 1;
     17                  END IF;
     18                  IF instr(clsrec.classlist, 'PE') > 0 THEN
     19                          pect:=pect + 1;
     20                  END IF;
     21                  IF instr(clsrec.classlist, 'Social') > 0 THEN
     22                          socialct:=socialct + 1;
     23                  END IF;
     24                  IF instr(clsrec.classlist, 'Science') > 0 THEN
     25                          sciencect:=sciencect + 1;
     26                  END IF;
     27                  IF instr(clsrec.classlist, 'Reading') > 0 THEN
     28                          readct:=readct + 1;
     29                  END IF;
     30                  IF instr(clsrec.classlist, 'Art') > 0 THEN
     31                          artct:=artct + 1;
     32                  END IF;
     33          END loop;
     34          dbms_output.put_line('Math   : '||mathct);
     35          dbms_output.put_line('PE     : '||pect);
     36          dbms_output.put_line('Social : '||socialct);
     37          dbms_output.put_line('Science: '||sciencect);
     38          dbms_output.put_line('Reading: '||readct);
     39          dbms_output.put_line('Art    : '||artct);
     40  END;
     41  /

     
    Math   : 3
    PE     : 3
    Social : 2
    Science: 2
    Reading: 1
    Art    : 1
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
     
  7. sandy5

    sandy5 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much.. But I would like to know what I should do,
    if I have more subjects (than what I mentioned in my table) in my class list..
    IF instr(clsrec.classlist, 'Math') > 0 THEN

    I am not sure of what all subjects the students can have in their class list.. to my knowledge there are more than 100 subjects that the students can select from.
    ex: math, advanced math,advanced geometry, science, biology, physical sciences etc., etc.,

    (they were never saved in a lookup table, so first we have to create one, with all the values in classlist of students table)

    so the student 115 might have taken biology, pe, advanced math, advanced geometry
    with Ms.Bee as their home room teacher.

    So, i am thinking I should split ; separated classes (get distinct subjects/classes) and save them in a temp table and then do a query
    against students table to get the count of students.

    I couldn't figure out a way to split classlist (; separated) and store each subject in a table.

    All your help is highly appreciated.

    Thanks
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Itis not really needed to create temp table for master list. This could be tried even in oracle 9i with straight SQL.
    here is the query.

    Group by CLASS

    Code (SQL):
    SQL> SELECT * FROM STD_CLS_TCR;

    STUDENT_ID CLASSES                                            TEACHER
    ---------- -------------------------------------------------- --------------------
           111 Math;Social;Science                                Ms.Reed
           112 Math;Science;PE                                    Mr.Smith
           113 PE;Social;Math                                     Ms.Reed
           114 Art;PE;Reading                                     Mr.Jones
           115 Math;Economics                                     Mr.Raj
           116 Economics;Accountancy                              Mr.Ben

    6 ROWS selected.

    SQL> SELECT CLASS , COUNT(*)  FROM
      2  (SELECT STR,TRIM(SUBSTR(STR, INSTR(STR, DELMTR, 1, LEVEL) + 1,
      3               INSTR(STR, DELMTR, 1, LEVEL + 1) - INSTR(STR, DELMTR, 1, LEVEL) - 1)) CLASS,
      4               LEVEL LV,
      5               LAG(LEVEL, 1, 0) OVER (PARTITION BY STUDENT_ID ORDER BY LEVEL) LG
      6               FROM
      7  (WITH REC AS (SELECT STUDENT_ID ,CLASSES, ';' DELMTR,TEACHER FROM STD_CLS_TCR)
      8              SELECT STUDENT_ID, DELMTR||CLASSES||DELMTR STR,
      9                     DELMTR,TEACHER,
     10                     LENGTH(CLASSES||DELMTR) -LENGTH(REPLACE(CLASSES||DELMTR,DELMTR)) CLSN
     11              FROM REC)
     12  CONNECT BY LEVEL < CLSN+1) Z
     13  WHERE LV<>LG
     14  GROUP BY CLASS  ;

    CLASS                                                  COUNT(*)
    ---------------------------------------------------- ----------
    Science                                                       2
    PE                                                            3
    Art                                                           1
    Economics                                                     2
    Math                                                          4
    Accountancy                                                   1
    Social                                                        2
    Reading                                                       1

    8 ROWS selected.

    SQL>
    Group by CLASS. TEACHER

    Code (SQL):
    SQL> SELECT CLASS , TEACHER ,COUNT(*)  FROM
    (SELECT STR,TRIM(SUBSTR(STR, INSTR(STR, DELMTR, 1, LEVEL) + 1,
      2    3               INSTR(STR, DELMTR, 1, LEVEL + 1) - INSTR(STR, DELMTR, 1, LEVEL) - 1)) CLASS,
      4               TEACHER,
      5               LEVEL LV,
      6               LAG(LEVEL, 1, 0) OVER (PARTITION BY STUDENT_ID ORDER BY LEVEL) LG
      7               FROM
      8  (WITH REC AS (SELECT STUDENT_ID ,CLASSES, ';' DELMTR,TEACHER FROM STD_CLS_TCR)
      9              SELECT STUDENT_ID, DELMTR||CLASSES||DELMTR STR,
     10                     DELMTR,TEACHER,
     11                     LENGTH(CLASSES||DELMTR) -LENGTH(REPLACE(CLASSES||DELMTR,DELMTR)) CLSN
     12              FROM REC)
     13  CONNECT BY LEVEL < CLSN+1) Z
     14  WHERE LV<>LG
     15  GROUP BY CLASS,TEACHER;

    CLASS                                                TEACHER                COUNT(*)
    ---------------------------------------------------- -------------------- ----------
    Social                                               Ms.Reed                       2
    PE                                                   Mr.Smith                      1
    Science                                              Ms.Reed                       1
    Science                                              Mr.Smith                      1
    PE                                                   Ms.Reed                       1
    Reading                                              Mr.Jones                      1
    Economics                                            Mr.Raj                        1
    Economics                                            Mr.Ben                        1
    Accountancy                                          Mr.Ben                        1
    Art                                                  Mr.Jones                      1
    Math                                                 Mr.Smith                      1
    Math                                                 Mr.Raj                        1
    Math                                                 Ms.Reed                       2
    PE                                                   Mr.Jones                      1

    14 ROWS selected.

    SQL>
     
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Very elegant.
     
    rajavu likes this.
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi Zargon,

    Thanks for the complement. :)
     
  11. sandy5

    sandy5 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thank you so much!!