Mysql - avg colums, then find the average of that

Databases
1

Here is a very simplified version of what I am trying to do…


SELECT
COUNT(*) AS qty,
AVG(p1) AS val1,
AVG(p2) AS val2, 
AVG(p3) AS val3
FROM `test_db`
WHERE cid = 5

This query finds all records in my “test_db” with a “cid” value of 5. It then counts how many records, and then averages each column so I can display as needed…

Example Results
qty = 7
val1 = 3.2
val2 = 4.3
val3 = 3.1

MY PROBLEM:
Now I need to take all the average values (val1, val2 & val3 but not the qty) and get the average from those and save it within another value (eg: totalaverage)…
I would like to do this within the query if possible, and not use any php scripting for that final calculation.

The results I would like are:
qty = 7
val1 = 3.2
val2 = 4.3
val3 = 3.1
totalaverage = 3.53

One additional problem. It is possible that some of the values (val1, val2, val3) are actually null, therefore it will not be as easy as just adding/dividing.

Is there any way to do this within MySQL? Or must I resort to doing that final calculation with PHP?

2 

select count(*) as qty,
       avg(p1) as val1,
       avg(p2) as val2, 
       avg(p3) as val3
       (avg(p1) + avg(p2) + avg(p3))/3.0 as totalaverage
  from test_db
 where cid = 5
3

Excellent - that was really close, but it was not considering that some of the values could have been null.
It’s ok to add everything, including the null’s - but a NULL result should not be part of the division. In this case - if P2 was null, then it should be divided by 2, not 3.

Based on your sample though - I was able to come up with something that should work.


select count(*) as qty,
       avg(p1) as val1,
       avg(p2) as val2, 
       avg(p3) as val3,
       (avg(p1) + avg(p2) + avg(p3))/(IF(ISNULL(p1), 0, 1) + IF(ISNULL(p2), 0, 1) + IF(ISNULL(p3), 0, 1)) as totalaverage
from test_db
where cid = 5

If anyone can see a better way to do this, kindly let me know…
@swampBoogie - Thanks for nudging me in the right direction… I appreciate it.

4

It turns out that NULL values can cause some troubles when adding columns with the + operator. My solution here was to check for null and replace with 0 when found. At the same time, my division checks were also incorrect, as they were not checking the “avg” of each column. Therefore I was getting unpredictable results.

Here is the completed SQL that has tested positive in all my tests so far.


select count(*) as qty,
IFNULL(AVG(p1),0) as val1,
IFNULL(AVG(p2),0) as val2,
IFNULL(AVG(p3),0) as val3,
(IFNULL(AVG(p1),0) + IFNULL(AVG(p2),0) + IFNULL(AVG(p3),0))/(IF(ISNULL(avg(p1)), 0, 1) + IF(ISNULL(avg(p2)), 0, 1) + IF(ISNULL(avg(p3)), 0, 1)) as totalaverage
FROM `test_db`
WHERE cid = 5