----- Original Message ----- From: "Keith Warren" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 26, 2004 5:15 PM Subject: Query Help
> I'm converting from LassoMySQL (MySQL 3.23.54) to the current version of > MySQL, and I'm running into a query problem. > > (sum(Rush)+sum(Rec)+sum(KO)+sum(Punt)+sum(Inter)+sum(Fum)+sum(BP)) as TDs > > Previously, this would produce a valid number. But now, I'm getting "NULL". > This is a sports database for a newspaper, and we calculate individual > player stats via MySQL. All of these fields are the different methods a > player can score; rushing, receiving, kick off, punt, etc. So obviously, > some fields will have null values. > > It appears that this query in MySQL 4.0.21 doesn't work because there are > null values in some of the fields. How can I get around this? > Have a look at the coalesce() function. http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Your query will change along these lines: (coalesce(sum(Rush),0)+coalesce(sum(Rec),0).... etc. In other words, for each quantity, coalesce will use that quantity if it is non-null but if it is null, it will use zero instead. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
