How to add ANY operator into an AVG in CASE SQL

We have a stored procedure that averages snow conditions on local reports and then shoots out a set percentage for the county. That county is put on an interactive map and shades of colors are applied.

In counties where there are only a few reports, the average is getting set to 0 even when there is one open business. This is a problem because the color is then indicating there are no open businesses in that county.

Our solution is to set the average to 25 if there is even one report with a condition of 'Poor', 'Fair', 'Good', 'Excellent' and to 50 if this more than one report with one of those conditions.

I tried to achieve the first objective using the ANY operator, but I'm unable to make this work. I'm also not sure how to achieve the second objective.

This is the current query:

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

Here are the sample results from the original query:

Name	average
County A	100
County B	75
County C	0
County D	0
County E	25

To achieve the first objective using the ANY() operator, this is what I tried to do:

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Past Peak' THEN 101
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        WHEN sn.Condition = ANY ('Poor', 'Fair', 'Good', 'Excellent' ) THEN 25
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

Error when trying ANY():

Incorrect syntax near '='.
Incorrect syntax near 'Poor'. Expecting '(' or 'SELECT'.
Incorrect syntax near 'THEN'.

This is my attempt using the IN() operator:

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Past Peak' THEN 101
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        WHEN sn.Condition IN ('Poor', 'Fair', 'Good', 'Excellent' ) THEN 25
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

Here are the errors when trying IN():

Incorrect syntax near 'IN'.
Incorrect syntax near 'THEN'. Expecting ',', 'AND', or 'Or'.

Solution

You seem to want something more like this:

case count(case when sn.Condition in ('Poor', 'Fair', 'Good', 'Excellent') then 1 end)
    -- if those are all the possibilities then just use count(*) instead
    when 1 then 25
    when 2 then 50
    else avg(case sn.Condition 
                 when 'Poor' then 25 
                 when 'Fair' then 50 
                 when 'Good' then 75 
                 when 'Excellent' then 100
                 else 0 end)
end as "average"

It also gives you a flavor of both types of case expressions.

You could also guarantee that the calculation can't dip below 25 by taking advantage of nulls which will always be ignored. Since the lowest contributor to the calculation is 25, an average lower than that is then impossible, although it could still evaluate to null in the case of no inputs. (else null is also the default behavior when not explicitly specified):

coalesce(avg(case sn.Condition 
            when 'Poor' then 25 
            when 'Fair' then 50 
            when 'Good' then 75 
            when 'Excellent' then 100
            else null end), 0) as "average"