compute avg from a result set

I have an SQL statement

select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3 from table;

now I would like to compute avg(result1,result2,result3)

But how? Can it be done within MySQL?
LVL 1
ladweinAsked:
Who is Participating?
 
racekCommented:
I don't count columns with NULL value into AVG

COL1   COL2  COL3
2          NULL   2
2          NULL   NULL
2            6      4
----------------------------
AVG
2            6      3
----------------------------
Total AVG = 18 / 6

select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3,
SUM(coalesce(col1,0) + coalesce(col2,0) + coalesce(col3,0)) / (COUNT(col1) + COUNT(col2) + COUNT(col3))  as avg_result
from table;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean like this:
select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3
, coalesce(avg(col1 + col2 + col3 ),0)  as avg_result
from table;

0
 
ladweinAuthor Commented:
This returns (for example)

result1 = 1.6863
result2 = 1.6471
result3 = 1.7379
avg_result = 5.0909

avg_result should be 1.6904 which is (result1 + result2 + result3)/3 ;if none of the resultx is NULL

I have tried to do it in Java but then getDouble() returns 0.0 for NULL values which is incorrect since I also have 0.0 as a result

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
momi_sabagCommented:
hi

try doing this :
select avg(result1 + result2 + result3)/3 as result from
( select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3
from table) as t


momi sabag
0
 
hans_vdCommented:
I think AngelIII just forgot to put '/3':

select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3
, coalesce(avg((col1 + col2 + col3) / 3 ),0)  as avg_result
from table;
0
 
racekCommented:
select coalesce(avg(col1),0) as result1, coalesce(avg(col2),0) as result2, coalesce(avg(col3),0) as result3,
SUM(coalesce(avg(col1),0) + coalesce(avg(col2),0) + coalesce(avg(col3),0)) / (COUNT(col1) + COUNT(col2) + COUNT(col3))  as avg_result
from table;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.