Get Avg Ignoring Null or Zero Values

When using the AVG() SQL function, is NULL part of the average or is it ignored?

The only aggregate function that doesn't ignore NULL values is COUNT(*). Even COUNT() ignores NULL values, if a column name is given.

Read more about it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

calculate the average by ignoring the 0 values in column

From the spark,

val w = Window.partitionBy("item","loc").orderBy("month").rangeBetween(-2, -1)
df.withColumn("month", 'month.cast("int"))
  .withColumn("avg", avg(when('qty =!= lit(0), 'qty)).over(w)).show()

+-----+-----+-----+----+---+----+
| item|  loc|month|year|qty| avg|
+-----+-----+-----+----+---+----+
|watch|delhi|    1|2020| 10| 0.0|
|watch|delhi|    2|2020|  0|10.0|
|watch|delhi|    3|2020| 20|10.0|
|watch|delhi|    4|2020| 30|20.0|
|watch|delhi|    5|2020| 40|25.0|
|watch|delhi|    6|2020| 50|35.0|
+-----+-----+-----+----+---+----+

finding average by ignoring null values from denominator in oracle sql

You can use nvl2():

(0.5*nvl(col1, 0) + 1.0*nvl(col2, 0) + 0.5*nvl(col3, 0) + 1.0*nvl(col4, 0),
 0.5*nvl2(col1, 1, 0) + 1.0*nvl2(col2, 1, 0) + 0.5*nvl2(col3, 1, 0) + 1.0*nvl2(col4, 1, 0)
)

Usually, I prefer coalesce() to nvl(). But nvl() seems reasonable when used in conjunction with nvl2().

Mysql AVG to ignore zero

Assuming that you might want to not totally exclude such rows (perhaps they have values in other columns you want to aggregate)

SELECT AVG(NULLIF(field ,0)) 
from table

SQL Server : ignore AVG NULL value

Aggregate functions such as AVG() ignore null values. So the result that you are getting indicates that:

• either salary is null on all rows of the table

• or the table has no row at all

If you want in that event to return something else, like 0, use COALESCE():

Select COALESCE(AVG(Salary), 0) from Employee

Ignore empty values on average math, but show it as zero on result

From your comment I understood, you have valField defined as varchar, so you can use next trick:

select 
    Code, 
    coalesce(avg(nullif(valField, '')), 0) as avg_value
from tbl 
group by Code;

Test the query on SQLize.online

Here I used NULLIF function for convert empty values to null before calculate the average

find AVG in Sqlite by ignoring null/empty value

You can adjust the WHERE clause. One simple method is:

SELECT id as dd,AVG(mark) as avg 
FROM  Mark_O 
WHERE mark > 0
GROUP BY id;

This does not allow explicitly '0' marks, though. If you want to allow that, then you need to know what the "missing" values are. If it is the empty string:

SELECT id as dd,AVG(mark) as avg 
FROM  Mark_O 
WHERE mark <> ''
GROUP BY id;

Note that both of these conditions also filter out NULL rows. However, they would not be counted for the AVG() anyway.

---

Related Topics