Home » RDBMS Server » Server Administration » Please Help : How to Average
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 ?
|
|
|
|
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.
|
|
|
Goto Forum:
Current Time: Mon Dec 23 05:50:24 CST 2024
|