Defaulting to 0 on SUM(some_field) results.

[LucL]

Hi Guys,

When I perform some query like.. "SELECT SUM(moneys.m_money) FROM users LEFT JOIN moneys ON moneys.m_user=users.u_id WHERE u_user='test'"

If there are no entries in the MONEYS table for the specific user, I get a blank (NULL) result for the SUM function. However, I would like to get a 0 result.

Is there any way to easily return 0 instead of NULL if the result is null?

Thanks Guys!
Luc L.

 

[LucL]

I should add that I'm actually using this on MAX instead of SUM.

 

[r937]

use COALESCE

SELECT [b]coalesce([/b]max(moneys.m_money)[b],0)[/b] 
          as max_money
  FROM users 
LEFT OUTER
  JOIN moneys 
    ON moneys.m_user = users.u_id 
 WHERE u_user = 'test'

r937.com | rudy.ca

 

[LucL]

Did the trick. Thanks a lot man!