How to get around nested aggregate functions using AVG/COUNT?

[kpauli]

How can I get the average value of the data in column A only if there are more than 3 numbers greater than zero? It’s basically the equivalent of
SELECT AVG(IIF(COUNT(IIF(columnA>0,1,0))>3,columnA,Null)) I think, but aggregate functions can’t be nested.

I need to get this average for each column of multiple columns all at once that can have different numbers of data greater than 0, or maybe none greater than zero.

Thanks.

 

[PHV]

A starting point:
Create a saved query named, say, qryGetCount:
SELECT columnA, Count(IIf(columnA>0,1,0) As CountA
FROM yourTable
GROUP BY columnA;
Then you can try something like this:
SELECT columnA, AVG(IIf(CountA>3,columnA,Null) As AvgA
FROM qryGetCount
GROUP BY columnA;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244