RE: Omit record from SQL AVG based on field value

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

I believe the reason for this problem is that just about any operation
that includes a NULL returns the value NULL. For example

3 + NULL Returns NULL
Field = NULL Returns NULL
Average(3,5,10, NULL) Returns NULL


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Friday, February 02, 2007 3:13 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Omit record from SQL AVG based on field value


I've been trying the case but it returns null for the average and I know
that's not correct.  I tried it in the detail portion also and it is
substituting for every value not just the ones in error.

Here's the last try at using case.
Cast(Avg(case when (locftst - locbtst) > 0
                then (locftst - locbtst)
                  else NULL end) as dec(20,6))

Rick

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
> Sent: Friday, February 02, 2007 1:58 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Omit record from SQL AVG based on field value
>
> NULL means "void of value" so it cannot be manipulated with
> any arithmetic operations, including SUM & AVG.  Think of it
> as being "skipped", which is what you're after in this case.
>
> Take a look at that example.
>
> There are number of problems with your statement.... like
> GROUP BY a single field when you're projecting multiple
> fields - that won't work.
>
> You're better of starting from scratch with your SQL
> statement.  Forget GROUP BY.  AVG all rows, making sure
> fields without value are cast to NULL using CASE clause.
>
> Elvis
>
> Celebrating 10-Years of SQL Performance Excellence
>
> -----Original Message-----
> Subject: RE: Omit record from SQL AVG based on field value
>
>
> Elvis,

Privileged and Confidential.  This e-mail, and any attachments there to,
is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information.  If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail.  You are hereby notified that any dissemination,
distribution or copying of this e-mail and/or any attachments thereto,
is strictly prohibited.