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 Resultsqty = 7val1 = 3.2val2 = 4.3val3 = 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.

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.

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.

codamedia
—
2014-03-20T15:23:35Z —
#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.