Returning NULL values for averages 1

[OmenChild]

I am attempting to write a code that will take the amounts for four weeks and average them out. Right now, if there are any zero values within any week, the average results comes up NULL. I have used Coalesce to replace the NULL values within the given weeks to 0, I don't know if this is affecting the averaging. Here is the code:

(SUM(A.NET_PAY) + SUM(B.NET_PAY) + SUM(C.NET_PAY) + SUM(D.NET_PAY)) /
(COUNT(COALESCE(A.NET_PAY,0)) + COUNT(COALESCE(B.NET_PAY,0)) + COUNT(COALESCE(C.NET_PAY,0)) +
COUNT(COALESCE(D.NET_PAY,0))) AS AVERAGE

Any help is much appreciated. Thanks

 

[r937]

coalesce the sums as well

i'm guessing there are instances when one of the tables is missing a value altogether

r937.com | rudy.ca

 

[OmenChild]

Thanks, I tried that, but it didn't like it. Gave me a Sysibm.COALESCE error. I don't know why, because coalesce worked fine before. And there weren't any tables missing values altogether, but they would be turned to NULL, and then I would turn the NULLS to 0's. If there are any 0's in any of the weeks it causes my averages column to populate with NULL instead of averaging for the weeks that do have values. This is the last piece of code that I need to make this query work the way I want it to. Thanks for the suggestion.

 

[r937]

( coalesce(SUM(A.NET_PAY),0) + 
  coalesce(SUM(B.NET_PAY),0) + 
  coalesce(SUM(C.NET_PAY),0) + 
  coalesce(SUM(D.NET_PAY),0) + ) / 
( COUNT(COALESCE(A.NET_PAY,0)) + 
  COUNT(COALESCE(B.NET_PAY,0)) + 
  COUNT(COALESCE(C.NET_PAY,0)) +
  COUNT(COALESCE(D.NET_PAY,0)) ) AS AVERAGE

where do A, B, C, D come from? are you self-joining the same table 4 times?


and anyhow, why aren't you using the AVG function?

when the query gets clumsy, it's often an indicator that there may be an easier way...

r937.com | rudy.ca

 

[OmenChild]

Thank you sooo much! That did it! Nice one. Like a dork I had the coalesce and the sum backward. Thank you very much.

 

[PHV]

Unless you really want to count null values as zero values, I'd use COALESCE(COUNT instead of COUNT(COALESCE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, in fact I don't think you have to use COALESCE in the COUNT stuff ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886