Solved

compute avg from a result set

Posted on 2006-10-23
6
950 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:ladwein
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:ladwein
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 6

Expert Comment

by:hans_vd
Comment Utility
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
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 
LVL 14

Accepted Solution

by:
racek earned 125 total points
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now