In Snowflake how can I return the mean of a column, where null values are treated as zero. So, if we have 3 values: 1, 2, NULL, I would like to return 1, not 1.5 as returned by AVG function.

CodePudding user response：

This can be done using COALESCE to replace null values with 0:

CREATE OR REPLACE TABLE foo AS 
  SELECT $1
    FROM VALUES ( 1 ), ( 2 ), ( NULL);
    
SELECT AVG(COALESCE($1, 0)) FROM foo;

CodePudding user response：

Using ZEROIFNULL function:

Returns 0 if its argument is null; otherwise, returns its argument.

SELECT AVG(ZEROIFNULL(col_name))
FROM tab;

CodePudding user response：

You have several options, choose one of the following:

SELECT AVG(v) AS "avg"
     , AVG(COALESCE(v, 0)) AS "avg_with_coalesce"
     , AVG(CASE WHEN v IS NULL THEN 0 ELSE v END) AS "avg_with_case"
     , AVG(IFF(v IS NULL, 0, v)) AS "avg_with_iff"
     , AVG(IFNULL(v, 0)) AS "avg_with_ifnull"
     , AVG(ZEROIFNULL(v)) AS "avg_with_zeroifnull"
  FROM VALUES (1), (2), (NULL) AS t(v);

Result: