MySQL average row value - how to exclude NULL rows from a count

Question

I have a simple table (test) where I need to execute some average values ​​(I want to get the average number of rows and the average number of columns).

id var1 var2 var3 1 7 NULL 3 2 10 NULL 6 

I notice that the MySQL AVG () function excludes NULL values ​​from the count (they are not considered 0) that I want.

Similarly, I want to get this result:

 var1 var2 var3 total 8.5 NULL 4.5 6.5 (ie the overall average is 6.5 *not* 4.3333) 

But my query looks like this:

 SELECT AVG(var1) AS var1, AVG(var2) AS var2, AVG(var3) AS var3, ( AVG(var1)+ AVG(var2)+ AVG(var3) )/3.0 AS metric_total FROM test 

which returns a total average value of 4.333.

Is it possible in one query to get the average number of rows that excludes NULL entries from the account in the same way as the AVG () function does?

Answer 1

How about this? beautiful and simple ...

 SELECT AVG(var1) AS var1, AVG(var2) AS var2, AVG(var3) AS var3, ( SUM(ifnull(var1,0))+ SUM(ifnull(var2,0))+ SUM(ifnull(var3,0)) ) / (COUNT(var1)+COUNT(var2)+COUNT(var3)) AS metric_total FROM test 

Answer 2

 SELECT avg1, avg2, avg3, ( (COALESCE(avg1, 0) + COALESCE(avg2, 0) + COALESCE(avg3, 0)) -- only add non-null values / -- division ( IF(avg1 IS NULL, 0, 1) + IF(avg2 IS NULL, 0, 1) + IF(avg3 IS NULL, 0, 1) ) -- number of non-null averages ) AS metric_total FROM ( SELECT (SELECT AVG(var1) FROM test) AS avg1, (SELECT AVG(var2) FROM test) AS avg2, (SELECT AVG(var3) FROM test) AS avg3 ) AS sub 

The subquery allows you to not repeat the function AVG() .

Good luck

Answer 3

You want the average of all var1, var2, and var3 to be in all columns. Maybe for each column a sub-selection is selected to get its average value, with each sub-selection being combined together. Down side this will only work if each column has the same number of nonzero fields

 SELECT AVG(FirstAvg) FROM ( SELECT AVG(var1) AS FirstAvg FROM test UNION SELECT AVG(var2) AS FirstAvg FROM test UNION SELECT AVG(var3) AS FirstAvg FROM test ) Sub1 

To fix this problem, maybe just AVG once per sub selects

 SELECT AVG(FirstAvg) FROM ( SELECT var1 AS FirstAvg FROM test UNION SELECT var2 AS FirstAvg FROM test UNION SELECT var3 AS FirstAvg FROM test ) Sub1 

To get average values ​​of other columns: -

 SELECT AVG(var1), AVG(var2), AVG(var3), AVG(FirstAvg) AS total FROM ( SELECT var1 AS var1, NULL AS var2, NULL AS var3, var1 AS FirstAvg FROM test UNION SELECT NULL AS var1, var2 AS var2, NULL AS var3, var2 AS FirstAvg FROM test UNION SELECT NULL AS var1, NULL AS var2, var3 AS var3, var3 AS FirstAvg FROM test ) Sub1 

Answer 4

instead of AVG(var1), AVG(var2) , etc. try using AVG(ISNULL(var1,0)), AVG(ISNULL(var2,0))