by

How To: SQL to Calculate Average of non-Zeroes from a List of Values

I don’t do much coding these days but here’s one I’ve done recently and thought about keeping it for future reference Smile

For a list of values, how to calculate the average only using the ones which are non-zeros?


CREATE FUNCTION dbo.fn_AverageNonZeroes 
(@para int, @parb int, @parc int, @pard int, @pare int)
RETURNS DECIMAL(4,2)
AS
BEGIN
    DECLARE @average DECIMAL(4,2)

    DECLARE @a DECIMAL(4,2)
    DECLARE @b DECIMAL(4,2)
    DECLARE @c DECIMAL(4,2)
    DECLARE @d DECIMAL(4,2)
    DECLARE @e DECIMAL(4,2)

    SET @a=CONVERT(DECIMAL(4,2),@para)
    SET @b=CONVERT(DECIMAL(4,2),@parb)
    SET @c=CONVERT(DECIMAL(4,2),@parc)
    SET @d=CONVERT(DECIMAL(4,2),@pard)
    SET @e=CONVERT(DECIMAL(4,2),@pare)

    IF @a>0 OR @b>0 or @c>0 or @d>0 or @e>0
     SELECT @average=(@a + @b + @c +@d + @e)
             /
             (0+
             CASE WHEN @a=0 THEN 0 ELSE 1 END +
             CASE WHEN @b=0 THEN 0 ELSE 1 END +
             CASE WHEN @c=0 THEN 0 ELSE 1 END +
             CASE WHEN @d=0 THEN 0 ELSE 1 END +
             CASE WHEN @e=0 THEN 0 ELSE 1 END
             )
     ELSE
         SELECT @average=0.0
    
     RETURN @average
END