• alex_pangcats (7/1/2009)


    Hi everyone,

    Another table (Teacher_Student_Classes) that contains dayId,teacher_name,student_name,date & classes(subject).

    ex. Teacher1 handle many classes(subjects)

    ex. Every class have many students.

    Now i want to create a query that shows how many number of students attended the class of teacher1 from one month.

    ex. teacher1 have a total 0f 20 students.

    teacher2 have a total of 50 studens.

    example results:

    Jan-1 Jan-2 Jan-3 .........

    teacher1 20 19 15

    teacher2 50 45 30

    The results something like a calendar type.

    thanks in advance. hope that my messages help you to understand my problem.

    SELECT Date,

    (select count(student_name) from Teacher_Student_Classes where teacher_name=t1 and date = 'jan') AS [t1],

    (select count(student_name) from Teacher_Student_Classes where teacher_name=t2 and date = 'Feb') AS [t2],

    (select count(student_name) from Teacher_Student_Classes where teacher_name=t3 and date = 'Mar') AS [t3]

    FROM Teacher_Student_Classes

    GROUP BY Date

    Tanx 😀