Query Avg / Ignore Null Values and Zeros

[gumby]

I would like my Avg(Calls.TotalCumScore) to ignore Null Values and
Zeros.



SELECT Calls.Monitor, Avg(Calls.TotalCumScore) AS AvgOfTotalCumScore,
Count(Calls.Completed) AS CountOfCompleted
FROM Calls
GROUP BY Calls.Monitor;

I think it is already ignoring the null values, but how do i get it to
ignore the Zeros?


David

 

[Michel Walsh]

Hi,


NULL are automatically disregarded.


Add a WHERE clause to remove the zeros.


SELECT Calls.Monitor, Avg(Calls.TotalCumScore) AS AvgOfTotalCumScore,
Count(Calls.Completed) AS CountOfCompleted
FROM Calls
WHERE TotalCumScore <> 0
GROUP BY Calls.Monitor;


The WHERE clause is evaluated BEFORE the aggregates, while the HAVING clause
is evaluated AFTER the aggregates. So, here, simply a WHERE clause will do,
since you want to remove the records before computing the average.


Hoping it may help,
Vanderghast, Access MVP

 

[[MVP] S.Clark]

Create a query that ignores null and queries.
Create a query, based on the first query, that performs the AVG.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Guest]

SELECT Calls.Monitor, Avg(Calls.TotalCumScore) AS AvgOfTotalCumScore,
Count(Calls.Completed) AS CountOfCompleted
FROM Calls
GROUP BY Calls.Monitor
HAVING Calls.TotalCumScore IS NOT NULL AND Calls.TotalCumScore <> 0;

 

[John Spencer]

The solution depends on whether or not your count and average are
independent of each other. Do you want to count Call.Completed if the
TotalCumScore is null or zero? IF not then

SELECT Calls.Monitor
, Avg(Calls.TotalCumScore) AS AvgOfTotalCumScore
, Count(Calls.Completed) AS CountOfCompleted
FROM Calls
WHERE TotalCumScore <> 0 and TotalCumScore is Not Null
GROUP BY Calls.Monitor

If the two are independent of each other then

SELECT Calls.Monitor
, Avg(IIF(Calls.TotalCumScore) = 0,Null, TotalCumScore)) AS
AvgOfTotalCumScore
, Count(Calls.Completed) AS CountOfCompleted
FROM Calls
GROUP BY Calls.Monitor

 

[gumby]

Thank you all. It works perfect each way.

David