[V4MD] Problem with AVG

[Keith Ulrich]

Hi Ruslan,

I'm having trouble with the AVG function. I use it frequently in queries to 
my database and it almost always works correctly. But for some reason it's 
failing on my current data. I've pasted the query below.

The query returns zero records. If I remove the AVG function from the 
query, it returns many records. If I substitute SUM for AVG, it still 
returns nothing.

Also, I tried removing all records except one from the PerformanceScore 
table. When I do that, the query correctly returns a single record. Let me 
know if you need me to send you my database.

The initial problem cropped up in Director MX, but I've doing my 
experimenting and troubleshooting in ViSQL. I'm using version 1.10 of 
Valentina.

Thanks for your help.

Keith Ulrich

=====================================

select Class.class_id ,
        Class.name ,
        Session.activity_id ,
        Activity.activity_name ,
        Session.student_id ,
        Student.fname ,
        Student.lname ,
        SessionDetail.sdetail_id ,
        SessionDetail.begin_date ,
        SessionDetail.begin_time ,
        Task.task_name ,
        Task.task_id ,
        ScoreType.score_name ,
        avg(PerformanceScore.value)
   from Student,
        Session,
        Activity,
        Group,
        SessionDetail,
        Task,
        PerformanceScore,
        ScoreType,
        Class,
        Roster
  where (Roster.classObjPtr = Class.recID) AND
        (Roster.studentObjPtr = Student.recID) AND
        (Session.studentObjPtr = Student.recID) AND
        (Session.activityObjPtr = Activity.recID) AND
        (Activity.group_id = Group.group_id) AND
        (SessionDetail.sessionObjPtr = Session.recID) AND
        (ScoreType.recID = PerformanceScore.scoreTypeObjPtr) AND
        (SessionDetail.recID = PerformanceScore.sessionDetailObjPtr) AND
        (SessionDetail.taskObjPtr = Task.recId) AND
        (Class.class_ID IN (2)) AND
        (Group.group_tag = 'PBR') AND
        (PerformanceScore.value is not NULL)
GROUP BY Class.class_id,
        Class.name,
        Session.activity_id,
        Activity.activity_name,
        Session.student_id,
        Student.fname,
        Student.lname,
        SessionDetail.sdetail_id,
        SessionDetail.begin_date,
        SessionDetail.begin_time,
        Task.task_name,
        Task.task_id,
        ScoreType.score_name
ORDER BY Class.class_id ,
        Class.name ,
        Session.activity_id ,
        Activity.activity_name ,
        Session.student_id ,
        Student.fname ,
        Student.lname ,
        SessionDetail.sdetail_id ,
        SessionDetail.begin_date ,
        SessionDetail.begin_time ,
        Task.task_name ,
        Task.task_id ,
        ScoreType.score_name