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,

I thought about using case to replace bad values with null but that will
still include the record in the count won't it?  Or, does it keep a
separate count of occurrences by field?  Maybe I'm making this harder
than it is.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, February 02, 2007 1:35 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Omit record from SQL AVG based on field value

Rick, I don't know where to start with your SQL statement,
it's too much work for Friday afternoon :(

I do believe that what you need is to simply ignore the
fields where you have no values by casting them to NULLs. 
You do this with CASE clause.
I googled and found this link that illustrates the point:

http://www.craigsmullins.com/ssu_0899.htm

See SQL statement #3.

If you can simplify your statement or at least narrow down
the problem, post back.

Elvis

Celebrating 10-Years of SQL Performance Excellence

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.