richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka) (05/17/89)
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
john@riddle.UUCP (Jonathan Leffler) (05/22/89)
In article <3958@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: > >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. > > >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 > > ##Better yet I'd like to say-- > > group total of points / average points of each ID > >end Basically, you have to do the counting yourself. Define a variabl (or two or three) and set it(them) to zero in the first page header block. In the before group of id block, increment the count of distinct ids; if you need to accumulate the sum of the points (I don't think you do, but ...) tally those up in the on every row block, remembering to zero the count in the before group of id block. Jonathan Leffler (john@sphinx.co.uk)