Average & Count Together

Question

I have seen this asked before, but I the solutions I have seen are not working for me. So I'm going to ask my scenario....I keep getting an error of Msg 130, Level 15, State 1, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery when I try to execute my query. I have tried several attempts. This is what I have tried

--Not working
Select Name,
coalesce(COUNT(CASE WHEN CompletedCall IS NULL THEN AVG(CallAttempt) END), 0)
FROM hotwire.calldatabase
GROUP BY Name
ORDER BY Name

--This is also not working
Select Name,
COUNT(CASE WHEN CompletedCall IS NULL THEN AVG(CallAttempt) Else 0 END)
FROM hotwire.calldatabase
GROUP BY Name
ORDER BY Name

--This is also not working
Select Name,
COUNT(CASE WHEN CompletedCall IS NULL THEN AVG(CallAttempt) Else null END)
FROM hotwire.calldatabase
GROUP BY Name
ORDER BY Name

Answer 1

Based on your comment above:

Select Name, AVG(CallAttemps)
FROM hotwire.calldatabase
WHERE CompletedCall IS NULL
GROUP BY Name
ORDER BY Name