Forum Stats

  • 3,826,361 Users
  • 2,260,636 Discussions
  • 7,896,916 Comments

Discussions

RETURN row with highest averages attendance

User_G3NSO
User_G3NSO Member Posts: 34 Green Ribbon

I have a query below that works but isn't completed. I need to narrow down the result set and return the 1 row, 

which the average number of students attending lectures is the highest.


If my calculations are correct it should be this row.


4    Debra    Downing    3    Design


Any help would greatly be appreciated


create table dept(  
  department_id     number(2),  
  department_name      varchar2(30),    
  constraint dept_pk primary key (department_id));
/

INSERT INTO dept(department_id, department_name)
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'PROGRAMMING' FROM DUAL UNION ALL
SELECT 3, 'DESIGN'  FROM DUAL;


CREATE TABLE teachers (
   teacher_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
  department_id NUMBER(2),
constraint department_id_fk foreign key (department_id) references dept (department_id),
constraint teacher_pk primary key (teacher_id));

INSERT INTO teachers (
teacher_id, first_name, last_name,
department_id)
SELECT 1, 'Ann', 'Abbott', 1 FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Boop', 1 FROM DUAL UNION ALL
SELECT 3, 'Charles', 'Caputo', 2 FROM DUAL UNION ALL
SELECT 4, 'Debra', 'Downing', 3 FROM DUAL;
CREATE TABLE course (
   course_id  number(*,0),
   course_name VARCHAR(25) NOT NULL,
  enrolled_students number(*,0) NOT NULL,
  teacher_id NUMBER(2),
  topic VARCHAR2(20),
constraint teacher_id_fk foreign key (teacher_id) references teachers (teacher_id),
constraint course_pk primary key (course_id));


INSERT INTO course (
course_id, course_name, enrolled_students, teacher_id, topic)
SELECT 1, 'C programming', 35, 1, 
'Programming' FROM DUAL UNION ALL 
SELECT 2, 'C programming', 28, 2, 
'Programming' FROM DUAL UNION ALL 
SELECT 3, 'Design', 50, 4, 
'Databases'  FROM DUAL UNION ALL 
SELECT 4, 'SQL', 50, 3, 
'Databases'  FROM DUAL UNION ALL 
SELECT 5, 'SQL', 50, 1, 
'Databases'  FROM DUAL UNION ALL 
SELECT 6, 'C programming', 45, 3, 
'Design'  FROM DUAL UNION ALL 
SELECT 7, 'PLSQL', 50, 4, 
'Databases'  FROM DUAL;


CREATE TABLE lectures (
   lecture_id  number,
   room_id  number,
   start_date DATE,
   end_date DATE,
   course_id  number,
   attending_students number,
   constraint course_id_fk foreign key (course_id) references course (course_id),
   constraint lectures_pk primary key (lecture_id));


INSERT INTO lectures (lecture_id, room_id, start_date, end_date,  course_id, attending_students)

SELECT 1, 1, 

TIMESTAMP '2022-06-20 09:00:00',

TIMESTAMP '2022-06-20 10:45:00',

3, 49 FROM DUAL UNION ALL 

SELECT 2, 1, 

TIMESTAMP '2022-06-21 09:00:00',

TIMESTAMP '2022-06-21 10:45:00',

3,48 FROM DUAL UNION ALL 

SELECT 3, 1, 

TIMESTAMP '2022-06-22 09:00:00',

TIMESTAMP '2022-06-22 10:45:00',

3,47 FROM DUAL UNION ALL 

SELECT 4, 2, 

TIMESTAMP '2022-06-20 11:30:00',

TIMESTAMP '2022-06-20 12:55:00',

4, 39 FROM DUAL UNION ALL 

SELECT 5, 2, 

TIMESTAMP '2022-06-21 11:30:00',

TIMESTAMP '2022-06-21 12:55:00',

4, 38 FROM DUAL UNION ALL 

SELECT 6, 2, 

TIMESTAMP '2022-06-22 11:30:00',

TIMESTAMP '2022-06-22 12:55:00',

4, 37 FROM DUAL UNION ALL 

SELECT 7,3, 

TIMESTAMP '2022-06-20 13:30:00',

TIMESTAMP '2022-06-20 15:55:00',

5, 23 FROM DUAL UNION ALL 

SELECT 8,3, 

TIMESTAMP '2022-06-21 13:30:00',

TIMESTAMP '2022-06-21 15:55:00',

5, 22 FROM DUAL UNION ALL 

SELECT 9,3, 

TIMESTAMP '2022-06-22 13:30:00',

TIMESTAMP '2022-06-22 15:55:00',

5, 21 FROM DUAL UNION ALL 

SELECT 10,4, 

TIMESTAMP '2022-06-22 17:30:00',

TIMESTAMP '2022-06-22 18:55:00',

7, 18 FROM DUAL UNION ALL 

SELECT 11,4, 

TIMESTAMP '2022-06-22 17:30:00',

TIMESTAMP '2022-06-22 18:55:00',

7, 17 FROM DUAL UNION ALL 

SELECT 12,4, 

TIMESTAMP '2022-06-22 17:30:00',

TIMESTAMP '2022-06-22 18:55:00',

7, 16 FROM DUAL; 


SELECT 
   t.teacher_id,
   t.first_name, 
   t.last_name,
   c.course_id,
   c.course_name
 FROM teachers t join course c on c.teacher_id = t.teacher_id
WHERE c.topic = 'Databases'
ORDER BY  t.teacher_id


Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    Hi, @User_G3NSO

    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.

    This question looks a lot like homework, so I'll try not to spoil it for you. First, what exactly do you want to average? Is it lectures.attending_students per teacher, regardless of how many courses those lectures are related to? If so, start by joining the course and lecture tables, and find the average attending_students per teacher_id. When you get that, you can join whatever other tables you need for the display you need.

    What do you want to do it there is a tie, that is, two or more teachers have the same highest average? In situations like that, people often want to display all the rows in the tie. If that's what you want, then use the analytic RANK function right after you compute the averages, to identify the top teacher.

    If you get stuck at any step, post your best attempt, and ask a specific question about it. As always, if the question involves an error, post the full error message, including line number,

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    In a separate query, identify the courses (by course_id) with the highest average attendance. This can be done by simple aggregation, then fetching the row (or rows!) with max average attendance.

    Then use this in an IN condition in your WHERE clause. Like this:

    SELECT
       t.teacher_id,
       t.first_name,
       t.last_name,
       c.course_id,
       c.course_name
     FROM teachers t join course c on c.teacher_id = t.teacher_id
    WHERE c.topic = 'Databases'
    AND   c.course_id in (                               -----------------
            select course_id                             --      |
            from   lectures                              --      |
            group  by course_id                          --    added
            order  by avg(attending_students) desc       --      |
            fetch  first row with ties                   --      |
          )                                              -----------------
    ORDER BY  t.teacher_id;
    

    If two (or more) courses are tied for highest average attendance, they will all be shown in the result. If different handling is desired for that special case, you will need to explain what it is.