SitePoint Sponsor

# Thread: NULL Multiplication and Averaging

1. ## NULL Multiplication and Averaging

Here's what I'm trying to do.

I need to multiply

\$one = \$A * \$X
\$two = \$B * \$Y
\$three = \$C * \$Z

Then I need to take the averages \$one, \$two, and \$three. Normally, this would be no big deal at all. However, I'm having tremendous problem because \$A, \$B, or \$C could be NULL (which PHP seams to interpret as being zero).

So let's say \$A is null, which in my script means "did not vote and should not be factored in).

\$A * \$X will end up being 0. Let's say \$two=10 and \$three=20. If we average all three numbers, we'll end up getting an average of 10. Well, for what I'm doing, this is totally inaccurate.

I only want to factor in the users who did vote. So the average I need would forget about \$one since it should be NULL and simply take (\$two + \$three) / 2.

Does anyone know how I should attack this?

Brandon

2. It's a bit long but it works.
PHP Code:
``` <?php \$one = \$A * \$X; \$two = \$B * \$Y; \$three = \$C * \$Z; \$number = 3; if(\$one == 0){ \$number = \$number-1; } if(\$two == 0){ \$number = \$number-1; } if(\$three == 0){ \$number = \$number-1; } //average \$average = (\$one + \$two + \$three)/\$number; echo \$average; ?> ```

3. What I did...could be simpler if an array was used instead of \$one - \$three:
PHP Code:
```  <?php  \$one   = 0; \$two   = 10; \$three = 20;  \$count = 0;  if (\$one != 0) {     \$count++; }  if (\$two != 0) {     \$count++; }  if (\$three != 0) {     \$count++; }  \$average = (\$one + \$two + \$three) / \$count; print \$average; ```
Heh just saw jestep. Did you read my mind or did I? :P

4. Thats funny. Almost identical coding. I also had the array idea in mind when I was writing mine.

5. Okay, this will help a ton. Basically, a NULL is zero in php it appears. That's unfortunate. Oh well.

Could you guys go ahead and post the array fix for this? I'm still trying to get a feel for this array thing even though I think I've read every article on the web regarding arrays.

Thanks a lot of the help!

Brandon

6. PHP Code:
``` <?php\$numbers = array(0, 10, 20);\$average = 0;\$l = \$c = count(\$numbers);for (\$i = 0; \$i < \$l; \$i++) {    if (\$numbers[\$i] == 0) {        \$c--;    }    \$average = \$average + \$numbers[\$i];}\$average = \$average / \$c;print \$average; ```

7. Originally Posted by brandondrury
Okay, this will help a ton. Basically, a NULL is zero in php it appears. That's unfortunate. Oh well.
Well NULL is nothing and you can't add nothing so PHP has to convert it to a zero before it can ru any math on it. How it is in all languages.

8. How it is in all languages.
Would you say it works this way in MySQL (assuming you call MySQL a "language")?

From my limited reading thus far, it appears that null really means null in MySQL.

However, it does make sense that you can't really apply math to "nothing".

Brandon

9. you might want to read this if you care about the hows and whys of php's dynamic type conversions. read the links they provide too.
http://www.php.net/manual/en/languag...e-juggling.php

10. Thanks for the link. That article/page helped gel the numerous bits of data I've had bouncing around my head this week. Cool.

Brandon

11. Originally Posted by brandondrury
... it appears that null really means null in MySQL.
it certainly does

one nice thing about sql is that aggregate functions like SUM() and AVG() will ignore NULLs in the column

heck, even certain scalar functions do too -- e.g. CONCAT_WS will (whereas CONCAT won't)

neat, eh?

12. PHP Code:
``` \$average = \$average / \$c;  ```
I'm guessing that the best way to protect against \$c being zero (and therefor causing big ugly error) is to put an if \$c equals 0, make \$c equal to 1.

Does this seam logical?

Brandon

13. Originally Posted by logic_earth
Well NULL is nothing and you can't add nothing so PHP has to convert it to a zero before it can ru any math on it. How it is in all languages.
In theory, an error should be thrown when you attempt to use null in a place where a number is expected.

Fortunately, modern languages do so much to prevent you from shooting yourself in the foot that they often perform an implicit cast to the most obvious integer value, that being 0.

I wouldn't count on that being the case however, and would do some checks myself.

14. Originally Posted by Viflux
In theory, an error should be thrown when you attempt to use null in a place where a number is expected.
one nice thing about sql is that aggregate functions like SUM() and AVG() will ignore NULLs in the column

this is by design

if you are averaging some numbers in a column, what makes more sense -- substituting 0 for the NULLs, or ignoring the NULLs in calculating the average?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•