[V4MD] Problem with AVG

[Ruslan Zasukhin]

On 12/6/04 6:40 PM, "Keith Ulrich" <keith.ulrich at verizon.net> wrote:

Hi Keith,

Wow, what a huge query :-)

It looks to be correct. I do not see mistakes.
Yes send me db.

> 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

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------