 Home » RDBMS Server » Server Administration » How do i obtain averages.
How do i obtain averages. Mon, 28 February 2000 06:32 Ant Messages: 13Registered: February 2000 Junior Member

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 #370868 is a reply to message #370865] Mon, 28 February 2000 12:37  Naveen Kumar V Messages: 16Registered: February 2000 Junior Member
can you be more specific.
Re: How do i obtain averages. [message #370870 is a reply to message #370865] Mon, 28 February 2000 12:45  Suresh Messages: 189Registered: 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: 13Registered: 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.

Thanks
Re: How do i obtain averages. [message #370889 is a reply to message #370865] Wed, 01 March 2000 13:06  John R Messages: 156Registered: 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: 156Registered: 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: 156Registered: 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: 156Registered: 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: 156Registered: 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->
 Previous Topic: oracle optimiser Next Topic: array
Goto Forum:

Current Time: Wed Oct 28 07:21:56 CDT 2020