# Mysql - avg colums, then find the average of that ....

• Mar 19, 2014, 16:07
codamedia
Mysql - avg colums, then find the average of that ....
Here is a very simplified version of what I am trying to do...
Code:

```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?
• Mar 20, 2014, 05:47
swampBoogie
Code:

```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```
• Mar 20, 2014, 07:51
codamedia
Quote:

Originally Posted by swampBoogie
Code:

```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```

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.
Code:

```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.
• Mar 20, 2014, 09:23
codamedia
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.
Code:

```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```