RE: A sum of a few columns in where clause?
* IvanLatysh > >> I've got query like this: > > >> "select count(ID) from TABLE where (TABLE.A + TABLE.B + TABLE.C + > >> TABLE.D) > 0" > > >> A,B,C,D are decimal values. > > >> The problem is, I think, with NULL columns. Often B,C and D don't > >> have decimal value, but NULL. > >> I thought, that this should be counted like zero. Am I wrong ? > > RB> NULL is not the same as zero. > > RB> If any of the field values are NULL, the result of the expression is > RB> NULL. > > Hey guys Is this so hard to help people instead of killing them ? I'm sorry, it was not my intention to "kill" anyone! > Use IFNULL(null_field, 0) > > And your query will be like: > select count(ID) from TABLE where (IFNULL(TABLE.A, 0) + > IFNULL(TABLE.B, 0) + IFNULL(TABLE.C, 0) > + IFNULL(TABLE.D,0)) > 0 In general, I agree that it is better to provide an example of how to do it, as you do here, but in this case I thought it may be a better solution to avoid having NULL's in the table in the first place. But I don't know if there are other reasons he have the NULL's... instead of trying to guess what he wanted, I just gave him the facts on something he was obviously confused about... -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A sum of a few columns in where clause?
>> I've got query like this: >> "select count(ID) from TABLE where (TABLE.A + TABLE.B + TABLE.C + >> TABLE.D) > 0" >> A,B,C,D are decimal values. >> The problem is, I think, with NULL columns. Often B,C and D don't >> have decimal value, but NULL. >> I thought, that this should be counted like zero. Am I wrong ? RB> NULL is not the same as zero. RB> If any of the field values are NULL, the result of the expression is RB> NULL. Hey guys Is this so hard to help people instead of killing them ? Use IFNULL(null_field, 0) And your query will be like: select count(ID) from TABLE where (IFNULL(TABLE.A, 0) + IFNULL(TABLE.B, 0) + IFNULL(TABLE.C, 0) + IFNULL(TABLE.D,0)) > 0 And I advice you - RTFM. --- Yours sincerely, Ivan Latysh. [EMAIL PROTECTED] http://ivan.yourmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A sum of a few columns in where clause?
* Przemyslaw Popielarski > I've got query like this: > > "select count(ID) from TABLE where (TABLE.A + TABLE.B + TABLE.C + > TABLE.D) > 0" > > A,B,C,D are decimal values. > > There are over 1 records where A is greater than 0, > about 1000 where B is greater than 0, > about 500 where C is greater than 0, > and about 300 where D is greater 0. > > I want to get a value of at least 1 of course. > But I get a very small value, near 50. > > The problem is, I think, with NULL columns. Often B,C and D don't have > decimal value, but NULL. > I thought, that this should be counted like zero. Am I wrong ? NULL is not the same as zero. If any of the field values are NULL, the result of the expression is NULL. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A sum of a few columns in where clause?
Hi, I've got query like this: "select count(ID) from TABLE where (TABLE.A + TABLE.B + TABLE.C + TABLE.D) > 0" A,B,C,D are decimal values. There are over 1 records where A is greater than 0, about 1000 where B is greater than 0, about 500 where C is greater than 0, and about 300 where D is greater 0. I want to get a value of at least 1 of course. But I get a very small value, near 50. The problem is, I think, with NULL columns. Often B,C and D don't have decimal value, but NULL. I thought, that this should be counted like zero. Am I wrong ? -- ./ premax ./ [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
