CODEWITHSUNDEEP
oracle-database
user2365541
user2365541

Reputation: 1

Creat view with a avg column

I need to create a view VIEW UOS_VU_STUDENT_AVERAGE, one of the column requries average GRADE, SQL:

CREATE VIEW UOS_VU_STUDENT_AVERAGE AS
SELECT STUDENT.FIRST_NAME, STUDENT.LAST_NAME, STUDENT_MODULE.GRADE
FROM STUDENT, STUDENT_MODULE
WHERE STUDENT_ID<120000001

How could I avg grade in this sql?

Upvotes: 0

Views: 272

Answers (3)

mtwaddell
mtwaddell

Reputation: 189

You'll need to do something like the following. I've made a few assumptions on the structure of the tables.

CREATE VIEW uos_vu_student_average AS
SELECT first_name, last_name,  AVG(grade) avg_grade
  FROM student, student_module
 WHERE student.student_id = student_module.student_id
   AND student.student_id < 120000001
 GROUP BY first_name, last_name;

In your example, and as was pointed out by a previous poster, you don't have a join in your original example so it would just average all the grades together, regardless of the student_id. The result would show everyone with the same average grade.

When doing aggregate functions inside a view you also need to assign the resulting column an alias, so you can reference it in some manner when performing DML against the view. In this case I assigned it avg_grade.

Upvotes: 0

Deniyal Tandel
Deniyal Tandel

Reputation: 632

You have to use "Group by" for any mathematical function like 

AVG ( [ ALL | DISTINCT ] expression )

for example ..

SELECT id, AVG(salary) from tablename GROUP BY filedname

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16915

try

CREATE VIEW UOS_VU_STUDENT_AVERAGE AS
SELECT STUDENT.FIRST_NAME, STUDENT.LAST_NAME, avg(STUDENT_MODULE.GRADE)
FROM STUDENT, STUDENT_MODULE
WHERE STUDENT_ID<120000001 
group by STUDENT.FIRST_NAME, STUDENT.LAST_NAME

as zerkms commented, there is no join condition, you probably need something like this:

CREATE VIEW UOS_VU_STUDENT_AVERAGE AS
SELECT STUDENT.FIRST_NAME, STUDENT.LAST_NAME, avg(STUDENT_MODULE.GRADE)
FROM STUDENT join STUDENT_MODULE on student_module.STUDENT_ID = student.id
WHERE STUDENT_ID<120000001 
group by STUDENT.FIRST_NAME, STUDENT.LAST_NAME

(I'm just guessing that the FK is on student_module.STUDENT_ID = student.id)

Upvotes: 1

Related Questions