Sql: Avg with Null Values

SQL: AVG with NULL Values

Use coalesce() to return the real value of zero for null columns:

select avg(coalesce(some_column, 0))
from ...

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

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

Calculate average of 4 numbers ignoring null values in SQL

You may unpivot the values using VALUES table-value constructor and then get the average of the values using AVG(). The AVG() function will ignore the NULL values:

SELECT AVG(x)
FROM (VALUES (12.0), (NULL), (NULL), (3.0)) v (x)

If the values are stored in a table, you need an additional APPLY operator:

SELECT *
INTO Data
FROM (VALUES
   (12.0, NULL, NULL, 5.0),
   (NULL, NULL, NULL, NULL),
   (1, 1, 1, 10.0)
) d (Col1, Col2, Col3, Col4)

SELECT *
FROM Data d
OUTER APPLY (
   SELECT AVG(Col) AS Col
   FROM (VALUES
      (d.Col1), (d.Col2), (d.Col3), (d.Col4)
   ) v (Col)   
) a

Result:

Col1  Col2 Col3 Col4 Col
-----------------------------
12.0  null null 5.0  8.500000
null  null null null null
1.0   1    1    10.0 3.250000

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: averaging with nulls

Aggregate functions (SUM, AVG, COUNT, etc) in SQL always automatically exclude NULL.

So SUM(col) / COUNT(col) = AVG(col) - this is great and consistent.

The special case of COUNT(*) counts every row.

If you make up an expression with NULLs: A + B where either A or B is NULL, then A + B will be NULL regardless of the other column being NULL.

When there are NULLs, in general, AVG(A + B) <> AVG(A) + AVG(B), and they will likely have different denominators, too. You would have to wrap the columns: AVG(COALESCE(A, 0) + COALESCE(B, 0)) to solve that, but perhaps also exclude the case where COALESCE(A, 0) + COALESCE(B, 0).

Based on your code, I would suggest:

select avg(coalesce(col1, 0) + coalesce(col2, 0)), count(col3) from table1
where coalesce(col1, col2) is not null -- double nulls are eliminated
group by SomeArbitraryCol
having avg(coalesce(col1, 0) + coalesce(col2, 0)) < 500 and count(col3) > 3
order by avg(coalesce(col1, 0) + coalesce(col2, 0)) asc;

Average in SQL Server with null value multiple columns

One possible option is to use VALUES table value constructor - to unpivot the columns into rows and to calculate the average value for all columns, excluding the columns with NULL values:

Statement:

SELECT t.empid, c.Average
FROM Dummy_tab t
CROSS APPLY (
   SELECT AVG(v.Month) AS Average
   FROM (VALUES (t.Month1), (t.Month2), (t.Month3)) v (Month)
) c

Result:

empid   Average
1       15
2       20
3       20

Calculate moving average with null values

Answer to question 1: put in the select condition

ISNULL(TGrads,0) AS TGRADS,
ISNULL(TStus,0) AS TSTUS,

Answer to question 2: I'd do this

(CASE WHEN SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) IS NOT NULL
AND SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) <>0
THEN (SUM(TGrads) OVER ( partition BY DistrictID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row )  / (SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) ) ) * 100
ELSE NULL END
   ) AS 3yrAvg 

I put null after "ELSE"...You can choose your default value.

SQL Server AVG function to return NULL if all values are NULL

The problem is that the Exit column doesn't have a data type that is compatible with the SUM function.

You can run this query to see that you indeed get NULL from SUM if all values are NULL (and a proper data type)

select sum(a) from (select convert(int, null) a union select null) a

Would null value change the average?

A NULL value will not change the average. The average of the two numbers is 90. In general, aggregation functions ignore NULL values -- including min(), max(), avg(), count() (except count(*)), and sum().

Here is a little rextester showing what happens.

---

Related Topics