Path: utzoo!utgpu!jarvis.csri.toronto.edu!rutgers!deimos.cis.ksu.edu!unmvax!tut.cis.ohio-state.edu!ucbvax!agate!helios.ee.lbl.gov!nosc!humu!uhccux!richardj
From: richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka)
Newsgroups: comp.databases
Subject: ISQL help on Informix
Keywords: ace, select
Message-ID: <3958@uhccux.uhcc.hawaii.edu>
Date: 17 May 89 07:32:58 GMT
Reply-To: richardj@uhccux.UUCP (Richard Jablonka)
Organization: University of Hawaii
Lines: 65


Howzit,

I am having a problem using the Ace Report Writer in Informix's ISQL for the
unix computer. The problem is that I want to find the total of groups made
by using the  an aggregate commands.  Here is the table:

bonuspoint--
   Date         date
   ID           char(10)
   Points       integer              

Everyday a person may receive bonus point or he may not.  The final grade is
based on the following equation:

Grade =  # of bonus points * ( total bonus pts for all ID's / # of ID's)

The output should look like the following

ID    12/12/89     12/13/89       12/14/89 ............ Total    FinalGrade
----  --------     --------       --------              -----    ----------
Bob      2           4               6                   12        1.20
Sue      3           -               7                   10        1.00
greg     10          -               -                   10        1.00
Leean    2           4               2                    8         .80



Looking at the equation, the problem is to find the number of ID's. Here is 
I have it:

Select

   Select points, date, id  
     from BonusPoint
       order by id

End 


Now how do I find how many groups of ids that I have?  In the format section 
of the Ace report I want to say:

Format

  group total of points /  (total of points) * group COUNT of ID

                                               ##you can't say that! (count of)

   ##Better yet I'd like to say--

  group total of points / average points of each ID

end


Thanks for your help!


                                     Aloha,
                                     Richard Jablonka  
    
                                     richardj@uhccux.hawaii.edu