We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# compute avg from a result set

on
Medium Priority
998 Views
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?
Comment
Watch Question

## View Solution Only

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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;

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

Commented:
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

Commented:
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;

Commented:
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;
Commented:
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;

Not the solution you were looking for? Getting a personalized solution is easy.

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile