How to delete 0 values but not blanks from a column in SQL Server 2008 programming?

Use AVG()

If you wish to ignore the 0 and include “blank” (if you mean NULL) to the base, you can make use of the following characteristic of the function:

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values.

So that

    CASE WHEN [column] = 0 THEN NULL -- Skip 0 when calculate the average
         WHEN [column] IS NULL THEN 0 -- Include blank as 0 value
         ELSE [column] END) AS Average
FROM [table]

Leave a Comment