0

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
0
$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;
0

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

0

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

0

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.