OraFAQ Forum: Server Administration » Please Help : How to Average

Home » RDBMS Server » Server Administration » Please Help : How to Average

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please Help : How to Average [message #373163]

Fri, 30 March 2001 10:43

Lisa Stone
Messages: 3
Registered: February 2001

Junior Member

I have a table Students which has data like:

Student No Maths English Physics Chemistry
1 80 80 82 89
2 78 75 83 76
3 70 70 78 90

How can I find average for each student ?

Report message to a moderator

Re: Please Help : How to Average [message #373165 is a reply to message #373163]

Fri, 30 March 2001 12:46

Raj.C.
Messages: 3
Registered: March 2001

Junior Member

If I understand your question well, here is the answer :
select student_no,(maths+english+physics+chemistry)/4
from students

This should work.

-Raj.C.

Report message to a moderator

Re: Please Help : How to Average [message #373178 is a reply to message #373163]

Mon, 02 April 2001 10:31

Eshwar. T
Messages: 1
Registered: April 2001

Junior Member

Hi,

From what u said to Raj, tht "It's not necessary that a student should take test in all subjects", my answer for ur requirement would be-

select (nvl(maths,0) + nvl(english,0) + nvl(physics,0) + nvl(chemistry,0)) /
(4-(decode(nvl(maths,-1),-1,1,0) + decode(nvl(english, -1),-1,1,0) +
decode(nvl(physics, -1),-1,1,0) + decode(nvl(chemistry, -1),-1,1,0)))
as Average from students

The output sequence would look something like this -

SQL> desc students
Name Null? Type
----------------------------------------------------- -------- ----------------------------------
STUD_ID NUMBER(4)
MATHS NUMBER(5)
ENGLISH NUMBER(5)
PHYSICS NUMBER(5)
CHEMISTRY NUMBER(5)

SQL> select * from students;

STUD_ID MATHS ENGLISH PHYSICS CHEMISTRY
--------- --------- --------- --------- ---------
1 45 43 23 78
2 98 54 23 45
3 98 76 34
4 43 59 23
5 20 30

SQL> select (nvl(maths,0)+nvl(english,0)+nvl(physics,0)+nvl(chemistry,0))/
2 (4-(decode(nvl(maths,-1),-1,1,0) + decode(nvl(english, -1),-1,1,0) +
3 decode(nvl(physics, -1),-1,1,0) + decode(nvl(chemistry, -1),-1,1,0)))
4 as Average from students
5 ;

AVERAGE
---------
47.25
55
69.333333
41.666667
25

Hope this would work for sure. & pls mail, if u find an other easier sql to solve this.

Eshwar.

Report message to a moderator

Previous Topic:	how to count no. of columns in a table??
Next Topic:	Can't get update to work!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 15:45:34 CDT 2024