Home » RDBMS Server » Server Administration » How do i obtain averages.
How do i obtain averages. [message #370865] Mon, 28 February 2000 06:32 Go to next message
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 #370868 is a reply to message #370865] Mon, 28 February 2000 12:37 Go to previous messageGo to next message
Naveen Kumar V
Messages: 16
Registered: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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->
Previous Topic: oracle optimiser
Next Topic: array
Goto Forum:
  


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