RE: Omit record from SQL AVG based on field value

Here is a slightly cleaner looking solution with the case statement in
the numerator.

Select
Sum(
 Case 
  when ThingIWantToAverage = ignorecondition then 0 
  else ThingIWantToAverage 
 end
)
/
sum(
 Case 
  when ThingIWantToAverage = 0 then 0 
  else 1 
 end
) 
from some_table

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

My general solution to this type of problem is something like

Select
Sum(ThingIWantToAverage)/sum(Case when ThingIWantToAverage = 0 then 0
else 1) from some_table

If you fields that you want to drop do not actually contain a 0 then you
will have to put a case to force them to 0 in the numerator.

Chris

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


I have an SQL statement that averages process times for a group of
records.  The issue I am having is that some records contain incomplete
data.  That is acceptable, but it's throwing the average way off.  Each
record has six timestamps, each one for a different step in the process.
It is possible that the process will be stopped before all steps have
been performed.

What I want is to calculate the average process time for each step but
omit the steps that were not performed.  For example, if a record has
time stamps for steps 1 - 4 but not steps 5 & 6, include the process
times into the average for steps 1 - 4 but not in steps 5 & 6.  I have
been working on this since yesterday but I'm just not seeing how to do
it without moving to RPG.  If anyone has any ideas I'd really appreciate
the help.

This is the statement:
with AvgTime as
    (select 'Average', 999999999,                                   
      CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,                         
         Cast(avg(locrtst - loctst) as dec(20,6)),                  
      CURRENT_TIMESTAMP, Cast(Avg(loctptst - locrtst) as dec(20,6)),
      CURRENT_TIMESTAMP, Cast(Avg(locbtst - locrtst) as dec(20,6)), 
      CURRENT_TIMESTAMP, Cast(Avg(locftst - locbtst) as dec(20,6)), 
      CURRENT_TIMESTAMP, Cast(Avg(loctftst - locftst) as dec(20,6)) 
    from acdtalib.lopcadt

    where locdte = '2007-02-01'                                      
    group by locdte)                                                
                                                                    
    select 'Application', locapp,                                   
      loctst as Received,                                           
      locrtst as parsed, locrtst - loctst as Parse_Time,         
      loctptst as Ack_XML, loctptst - locrtst as Ack_XML_Time,      
      locbtst as Booked, locbtst - locrtst as Booking_Time,

      locftst as Funded, locftst - locbtst as Funding_Time,

      loctftst as Processed_XML, loctftst - locftst as
Processed_XML_Time  
   from acdtalib.lopcadt

   where locdte = '2007-02-01'

   Union All

   select * from AvgTime

   order by 1 Desc;    

Rick Chevalier
AmeriCredit
Enterprise Solutions
817-525-7178



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.