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.