Home » SQL & PL/SQL » SQL & PL/SQL » Display average as part of result (19.2)
Display average as part of result [message #686142] Thu, 23 June 2022 05:26 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I have the following case, which is working perfectly. I'm unsure how to display the average as part of the result set. Any help would be greatly 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'
AND   c.course_id in (                               
        select course_id                          
        from   lectures                              
        group  by course_id                          
        order  by avg(attending_students) desc      
        fetch  first row with ties                   
      )                                             
ORDER BY  t.teacher_id;


Re: Display average as part of result [message #686143 is a reply to message #686142] Thu, 23 June 2022 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the average you are talking about is "avg(attending_students)" then (for instance):
TEST> WITH
  2    lect as (
  3      select course_id, avg(attending_students) avg_std,
  4             rank() over (order by avg(attending_students) desc) rk
  5      from   lectures
  6      group  by course_id
  7    )
  8  SELECT
  9     t.teacher_id,
 10     t.first_name,
 11     t.last_name,
 12     c.course_id,
 13     c.course_name,
 14     l.avg_std
 15   FROM teachers t join course c on c.teacher_id = t.teacher_id
 16        join lect l on l.course_id = c.course_id
 17  WHERE c.topic = 'Databases'
 18  AND   l.rk = 1
 19  ORDER BY  t.teacher_id
 20  /
TEACHER_ID FIRST_NAME      LAST_NAME        COURSE_ID COURSE_NAME        AVG_STD
---------- --------------- --------------- ---------- --------------- ----------
         4 Debra           Downing                  3 Design                  48

1 row selected.
Re: Display average as part of result [message #686146 is a reply to message #686143] Thu, 23 June 2022 06:51 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
Perfect. Thanks
Previous Topic: orapki: add crt and key to wallet
Next Topic: How to update multiple rows with different values from another table in the same query?
Goto Forum:
  


Current Time: Thu Mar 28 16:16:46 CDT 2024