Re: sql query problem HELP

"Keith" <klau_at_mindless.com> wrote in message news:9mlp30$8je$3_at_thebe.syd.dav.net.au...
> I have a DB called students
> STUID STUNAME MAJOR CREDITS
> ----- ------------------------------ --------------- ----------
> s1001 Smith, Tom History 90
> s1010 Burns, Edward Art 63
> s1015 Jones, Mary Maths 42
> s1002 Chin, Ann Maths 36
> s1020 Rivera, Jane CSC 15
> s1013 McCarthy, Owen Maths 9
>
> I need to find the average of the majors, so I tried
>
> select major, avg(credits) from students
> group by major;
>
> MAJOR AVG(CREDITS)
> --------------- ------------
> Art 63
> CSC 15
> History 90
> Maths 29
>
> but what I'm finding trouble is, doing the calculation of the average and
> then comparing the average back to the original credits and print out all
> the people with a credit mark that is lower than the average of the major.
>
> I wanted to do this all in one sql statement, currently I can only do it
if
> I put the average in another database.
>
> Please help
> thanks
> Keith
>
>

currently I can only do it if
> I put the average in another database.
Come on, you can't be serious about this. Are you serious?

If you are serious buy a book on sql.

select student
from students s
where credits <
(select avg(credit)
 from students s1
 where s1.major = s.major
)

One statement is, IMO, not possible, as there is no relationship between the 2 resultsets.
One sql script is definitely possible.
Most people here don't like to do other peoples homework, and your question definitely is of the homework type.

Regards,
Sybrand Bakker, Senior Oracle DBA Received on Mon Sep 03 2001 - 15:01:15 CDT