I've been working on trying to solve the problem of calculating the average of a row of numbers in which some of the cells have a null value. I'm convinced there's no easy way to do this. I found an example on the net that I have been working from but I can't seem to get that to work either. The example is:

``````\$sql = mysql_query("SELECT *,
(COALESCE(example, 0)
+ COALESCE(example2, 0)
+ COALESCE(example3, 0))
/
(3 -
(COALESCE(example - example, 1)
+ COALESCE(example2 - example2, 1)
+ COALESCE(example3 - example3, 1))
) AS row_avg FROM attrib1 WHERE recid=" .\$recordid) or die(mysql_error());``````

I'm not sure what exactly this query is doing, and thus equally unsure of how to use it do my advantage. Any input would greatly appreciated.

2
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by CFROG
``````\$query = 'SELECT somevalue FROM sometable WHERE somevalue IS NOT NULL';
\$res = mysql_query(\$query);
if (!\$res) {
die(mysql_error());
}
\$count = mysql_num_rows(\$res);
\$total = 0;
while(\$row = mysql_fetch_assoc(\$res)) {
\$total += intval(\$row['somevalue']);
}
\$avg = (\$count === 0) ? 0 : \$total / \$count;``````

There's no doubt that this one has me confused but wouldn't that work for only one column of data?

There's no doubt that this one has me confused but wouldn't that work for only one column of data?

I intentionally made my query vague so you can modify it to suit your needs. I was just showing you the concept of "IS NOT NULL". All of that COALESCE crap is unnecessary if you're working with single fields

I'm trying to work with data across multiple fields for a single record.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.