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.

Recommended Answers

All 4 Replies

$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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.