Postgres Average calculation ignores null

Question

This is my postgres table

 name   | revenue   
--------+---------  
 John   |     100  
 Will   |     100  
 Tom    |     100   
 Susan  |     100   
 Ben    |        
(5 rows)

Here, when I calculate average for revenue, It returns 100, which is clearly not the case and sum/count, which is 400/5 is 80. Is this behaviour by conventional design or am I missing the point?

I know I could change null to 0 and process as usual . But, given the default behaviour, is this intentional and preferred way of calculating average.

Answer 1

This is both intentional and perfectly logical. Remember that NULL means that the value is unknown.

It might, for instance, represent a value which will be filled in at some future date. If the future value turns out to be 0, the average will be 400 / 5 = 80, as you say; but if the future value turns out to be 200, the average value will be 600 / 5 = 120 instead. All we can know right now is that the average of known values is 400 / 4 = 100.

If you actually know that you have 0 revenue for this item, you should store 0 in that column. If you don't know what revenue you have for that item, you should exclude it from your calculations, which is what Postgres, following the SQL Standard, does for you.

If you can't fix the data, but it is in fact a case that all NULLs in this table should be treated as 0 - or as some other fixed value - you can use a COALESCE inside the aggregate:

 SELECT AVG(COALESCE(revenue, 0)) as forced_average