Home » RDBMS Server » Server Administration » How do i obtain averages.
| How do i obtain averages. [message #370865] |
Mon, 28 February 2000 06:32  |
Ant
Messages: 13
Registered: February 2000
|
Junior Member |
|
|
I am new to SQL, can someone please help me with the following problem.
Q - By listing the averages, show whether the over 25's students (from todays date) gain higher marks than the under 25's students.
The tables i am using are set out as below
STUDENT
*regno
name
tutor
coursecode
d.o.b
RESULT
*regno
*mcode
cwkmark
exammark
Thanks very much
Ant
|
|
|
|
|
|
| Re: How do i obtain averages. [message #370870 is a reply to message #370865] |
Mon, 28 February 2000 12:45  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
select 'Above 25' age,avg(b.mark) from student a,result b
where a.regno=b.regno
and (sysdate-dob)/365>25
union
select 'Below 25' age,avg(c.mark) from student a,result c
where a.regno=c.regno
and (sysdate-dob)/365<25
/
|
|
|
|
| Re: How do i obtain averages. [message #370873 is a reply to message #370868] |
Mon, 28 February 2000 14:20 |
Ant
Messages: 13
Registered: February 2000
|
Junior Member |
|
|
More specific - I'll try.
I need to find out if the students aged over 25 got a higher average coursework mark that the students aged less than 25.
Hope this will help you to help me ;-)
Thanks
|
|
|
|
| Re: How do i obtain averages. [message #370889 is a reply to message #370865] |
Wed, 01 March 2000 13:06 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, using a simplified set of tables:
Student:
Id Number
Age Number
and Result
Id Number
Mark Number
the code
select sign(s.age-25) ,avg(r.mark) Avg
from Student s,(select id,avg(mark) mark from result group by id) r
where s.id=r.id
group by sign(s.age-25)
Does the job quite nicely. If you want to format the result better, then:
select decode(to_char(sign(s.age-25)),'-1','<25','1','>25') Age,
avg(r.mark) Avg
from Student s,
(select id,avg(mark) mark from resultgroup by id) r
where s.id=r.id
group by decode(to_char(sign(s.age-25)),'-1','<25','1','>25')
Looks messier, but gives nicer output.
|
|
|
|
| Re: How do i obtain averages. [message #370890 is a reply to message #370865] |
Wed, 01 March 2000 13:06 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, using a simplified set of tables:
Student:
Id Number
Age Number
and Result
Id Number
Mark Number
the code
select sign(s.age-25) ,avg(r.mark) Avg
from Student s,(select id,avg(mark) mark from result group by id) r
where s.id=r.id
group by sign(s.age-25)
Does the job quite nicely. If you want to format the result better, then:
select decode(to_char(sign(s.age-25)),'-1','<25','1','>25') Age,
avg(r.mark) Avg
from Student s,
(select id,avg(mark) mark from resultgroup by id) r
where s.id=r.id
group by decode(to_char(sign(s.age-25)),'-1','<25','1','>25')
Looks messier, but gives nicer output.
|
|
|
|
| Re: How do i obtain averages. [message #370891 is a reply to message #370865] |
Wed, 01 March 2000 13:07 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, using a simplified set of tables:
Student:
Id Number
Age Number
and Result
Id Number
Mark Number
the code
select sign(s.age-25) ,avg(r.mark) Avg
from Student s,(select id,avg(mark) mark from result group by id) r
where s.id=r.id
group by sign(s.age-25)
Does the job quite nicely. If you want to format the result better, then:
select decode(to_char(sign(s.age-25)),'-1','<25','1','>25') Age,
avg(r.mark) Avg
from Student s,
(select id,avg(mark) mark from resultgroup by id) r
where s.id=r.id
group by decode(to_char(sign(s.age-25)),'-1','<25','1','>25')
Looks messier, but gives nicer output.
|
|
|
|
| Re: How do i obtain averages. [message #370892 is a reply to message #370865] |
Wed, 01 March 2000 13:07 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, using a simplified set of tables:
Student:
Id Number
Age Number
and Result
Id Number
Mark Number
the code
select sign(s.age-25) ,avg(r.mark) Avg
from Student s,(select id,avg(mark) mark from result group by id) r
where s.id=r.id
group by sign(s.age-25)
Does the job quite nicely. If you want to format the result better, then:
select decode(to_char(sign(s.age-25)),'-1','<25','1','>25') Age,
avg(r.mark) Avg
from Student s,
(select id,avg(mark) mark from resultgroup by id) r
where s.id=r.id
group by decode(to_char(sign(s.age-25)),'-1','<25','1','>25')
Looks messier, but gives nicer output.
|
|
|
|
| Re: How do i obtain averages. [message #370894 is a reply to message #370865] |
Thu, 02 March 2000 05:05 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Well, using a simplified set of tables:
Student:
Id Number
Age Number
and Result
Id Number
Mark Number
This code does the job quite nicely
select sign(s.age-25) ,avg(r.mark) Avg
from Student s,(select id,avg(mark) mark from result group by id) r
where s.id=r.id
group by sign(s.age-25)
If you want to format the result better, then:
select decode(to_char(sign(s.age-25)),'-1','<25','1','>25') Age,
avg(r.mark) Avg
from Student s,
(select id,avg(mark) mark from resultgroup by id) r
where s.id=r.id
group by decode(to_char(sign(s.age-25)),'-1','<25','1','>25')
Looks messier, but gives nicer output.
This method will give the results of people aged 25 exactly in a 3rd column, but the original problem did just say over 25's and under 25's
8->
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 14:39:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
