Averaging a value except when 0

From: Steven Reid <macreid_at_primus.com.au>
Date: 2000/05/07
Message-ID: <3914d2c3@news.iprimus.com.au>#1/1

Hi,

I have a database that records the score that a student has received for a test.
A value of 0 has been used to signify that that student did not do that test.

What I am trying to do is get averages of both the overall and the individual.
So if a zero has been entered, do not use it for the averaging but I still need to keep it in the database for reporting purposes.

I am using Oracle 8.0.5 and VB to access the database.

Student_Number Question Score date

      1                            1                0    1/1/00
      1                            2                5    1/1/00
      2                            1                3    1/1/00
      2                            2                5    1/1/00
      1                            1                3    2/1/00
      1                            2                4    2/1/00
      2                            1                4    2/1/00
      2                            2                4    2/1/00

What I want is to be able to get the following:

                        Date        Question     Rating
Overall               1/1/00         1              3
                                             2              5
Individual 1         1/1/00        1              0
                                             2              5

etc.

Overall                All Dates    1              3.33
                                             2              4.5
Indivual 1            All Dates    1               3
                                             2               4.5

Any help VERY MUCH appreciated.

Steve Received on Sun May 07 2000 - 00:00:00 CDT