sql query problem HELP

From: Keith <klau_at_mindless.com>
Date: Fri, 31 Aug 2001 02:19:37 +1000
Message-ID: <9mlp30$8je$3@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 Received on Thu Aug 30 2001 - 11:19:37 CDT