Ignoring zeros to complete average calculation

[OmenChild]

Hello all,
I am attempting to average out results of a query I created. Right now, if any of the fields contain a zero, the resulting average field comes up NULL. I need to somehow make it so the function ignores the zeros and only calculates the actual amounts. Any ideas? Here is the portion of the code that is performing the average:

SELECT
A.COMPANY, A.PAYGROUP, A.PAYCHECK_NBR, A.OFF_CYCLE, A.EMPLID, A.EMPL_RCD, A.NAME,
A.DEPTID, A.SSN, A.PAY_END_DT,

COALESCE(A.NET_PAY,0) AS WEEK_1,

CASE WHEN B.PAY_END_DT IS NULL THEN '2007-10-28' ELSE B.PAY_END_DT END AS PAY_END_DT,

COALESCE(B.NET_PAY,0) AS WEEK_2,

CASE WHEN C.PAY_END_DT IS NULL THEN '2007-10-21' ELSE C.PAY_END_DT END AS PAY_END_DT,

COALESCE(C.NET_PAY,0) AS WEEK_3,

CASE WHEN D.PAY_END_DT IS NULL THEN '2007-10-14' ELSE D.PAY_END_DT END AS PAY_END_DT,

COALESCE(D.NET_PAY,0) AS WEEK_4,

(SUM(A.NET_PAY) + SUM(B.NET_PAY) + SUM(C.NET_PAY) + SUM(D.NET_PAY))/4 AS AVERAGE

FROM NET_PAY A LEFT OUTER JOIN NET_TMP_ONE B
ON A.EMPLID = B.EMPLID
LEFT OUTER JOIN NET_TMP_TWO C
ON A.EMPLID = C.EMPLID
LEFT OUTER JOIN NET_TMP_THREE D
ON A.EMPLID = D.EMPLID

GROUP BY A.COMPANY, A.PAYGROUP, A.PAYCHECK_NBR, A.OFF_CYCLE, A.EMPLID, A.EMPL_RCD, A.NAME,
A.DEPTID, A.SSN, A.PAY_END_DT,

COALESCE(A.NET_PAY,0),

CASE WHEN B.PAY_END_DT IS NULL THEN '2007-10-28' ELSE B.PAY_END_DT END,

COALESCE(B.NET_PAY,0),

CASE WHEN C.PAY_END_DT IS NULL THEN '2007-10-21' ELSE C.PAY_END_DT END,

COALESCE(C.NET_PAY,0),

CASE WHEN D.PAY_END_DT IS NULL THEN '2007-10-14' ELSE D.PAY_END_DT END,

COALESCE(D.NET_PAY,0)

I'm not sure if the CASE or COALESCE functions are causing the problem or if there is simply a piece of code I am missing so it will ignore any 0 values and still perform the average on fields that have actual amounts other than 0.

Thank you all for your help!

 

[gmmastros]

Try this....

[tt][blue]
(SUM(A.NET_PAY) + SUM(B.NET_PAY) + SUM(C.NET_PAY) + SUM(D.NET_PAY))/ (Sign(A.Net_Pay) + sign(B.Net_Pay) + Sign(C.NetPay) + Sign(D.NetPay)) AS AVERAGE
[/blue][/tt]

The sign function returns 1 for any value greater than 0. 0 for values = 0, -1 for values less than 0, and NULL if the parameter is NULL.

In this case, I wouldn't expect any values less than 0 so you shouldn't need to worry about that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OmenChild]

Thank you gmmastros,
But I need to ignore the 0's that are currently there so the total can average out. Right now if there are any fields that have a value of 0, then the end result comes up NULL. Basically all four weeks have to have actual numbers in order for the averaging to work. If any of the weeks have a value of 0, then the average field comes up NULL. I need to find a way that will make the function ignore the zeros and only perform the averages on the weeks that contain actual values, yet still averaging the total by four weeks. Does that make sense?

 

[gmmastros]

Just so I understand...

If there are 3 values, and the 4th is NULL, you want the average of the 3, right? If so, try this...

[tt][blue]
(SUM(Coalese(A.NET_PAY, 0)) + SUM(Coalesce(B.NET_PAY, 0)) + SUM(Coalesce(C.NET_PAY, 0)) + SUM(Coalesce(D.NET_PAY, 0)))/ (Sign(Coalesce(A.Net_Pay, 0)) + sign(Coalesce(B.Net_Pay, 0)) + Sign(Coalesce(C.NetPay, 0)) + Sign(Coalesce(D.NetPay, 0))) AS AVERAGE[/blue][/tt]

The idea here is...

If a value is NULL or 0, then the numerator will add 0 to the sum. For the denominator, if a value is 0 or NULL, then 0 will be added, otherwise, 1 will be added.

Ex:

10.10
12.30
14.80
0

10.10 + 12.30 + 14.80 + 0
-------------------------
Sign(10.10) + Sign(12.30) + Sign(14.80) + Sign(0)


10.10 + 12.30 + 14.80 + 0
-------------------------
1 + 1 + 1 + 0


10.10 + 12.30 + 14.80 + 0
-------------------------
3

You should get the correct average. Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JarlH]

Are you really sure that the value 0 (zero) makes the sum to be NULL? (SUM-ing NULL's gives the result NULL.)

If you have problems with NULL's, use COALESCE!!!

I.e. SUM(COALESCE(A.NET_PAY,0)...

 

[OmenChild]

I'm positive the 0's were the cause for the NULL values being returned. With all the help you all have given me, I have managed to get it to work using COALESCE, so thank you very very much. Unfortunately, even though the query is working, it is changing the average results. Before I made the SIGN adjustment, the results would either come back as NULL or come back with an amount. I have pasted examples of the results below:

(SUM(A.NET_PAY) + SUM(B.NET_PAY) + SUM(C.NET_PAY) + SUM(D.NET_PAY))/4 AS AVERAGE[/color red]

WEEK_2 PAY_END_DT WEEK_3 PAY_END_DT WEEK_4 AVERAGE
3833.34 10/21/2007 3274.63 10/14/2007 3274.63 3553.98
2737.44 10/21/2007 2737.44 10/14/2007 2737.45 2737.4425
0 10/21/2007 3168.25 10/14/2007 4397.8 NULL
406.34 10/21/2007 406.34 10/14/2007 406.34 1252.955


The results I got below are after I added SIGN and COALESCE to my aggregate function.

(SUM(COALESCE(A.NET_PAY,0)) + SUM(COALESCE(B.NET_PAY,0)) + SUM(COALESCE(C.NET_PAY,0))
+ SUM(COALESCE(D.NET_PAY,0))) / (SIGN(COALESCE(A.NET_PAY,0)) + SIGN(COALESCE(B.NET_PAY,0))
+ SIGN(COALESCE(C.NET_PAY,0)) + SIGN(COALESCE(D.NET_PAY,0))) AS AVERAGE[/color red]

WEEK_2 PAY_END_DT WEEK_3 PAY_END_DT WEEK_4 AVERAGE
3833.34 10/21/2007 3274.61 10/14/2007 3274.63 3.55398
2737.44 10/21/2007 2737.44 10/14/2007 2737.45 2.7374425
0 10/21/2007 3168.25 10/14/2007 4397.8 3.39516
406.34 10/21/2007 406.34 10/14/2007 406.34 1.252955


As you can see when I attempt to simply sum the results and average it out. I receive NULL values if there is a 0 within any of the given weeks. Once I make the function changes, I no longer receive NULL values, but instead the position of the decimal point shifts. ??????? I'm at a loss. I can't figure out why it would shift the decimal point. Anyone have any ideas??

 

[gmmastros]

What is the data type of the NET_PAY column? I suspect that it is varchar or some other type of string. I would recommend a convert to numeric before adding the values together.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OmenChild]

Hey George,
The initial query would return NULL for any areas where NET_PAY or PAY_END_DT were blank. I needed to convert the NULL values so I could calculate the averages. So I used the COALESCE function to replace the NULL's with 0's. After I did that, the average column would come up NULL if any of the four weeks contained a 0 in the NET_PAY field. So I used the SIGN function in the hope that this would cause the 0's to be ignored and the weeks where there were amounts would be the only ones calculated. Unfortunately, this caused the decimal point to shift. So that is where I am at. I'll keep playing around with the code, and if anyone has any other suggestions, I will be more than happy to try them out. Thank you all again for your help, this is a great forum!!!

 

[gmmastros]

Ya know... I was completely blinded by the fact that you were manually averaging data, and completely ignored that you were grouping data and averaging the sums.

So, here's the thing. You can remove the coalesces because sum will ONLY add non-null values anyway. Essentially, nulls are treated like 0 when you use SUM. There is another aggregate function that may help, COUNT.

I recommend that you try....

[tt][blue](SUM(A.NET_PAY) + SUM(B.NET_PAY) + SUM(C.NET_PAY) + SUM(D.NET_PAY)) / (Count(A.NET_PAY) + Count(B.NET_PAY) + Count(C.NET_PAY) + Count(D.NET_PAY)) AS AVERAGE [/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom