SUM() Help

[n3mesis125]

Hey FOlks,

 

How can I fix an error so that if I'm using SUM() with two tables and lets say Table B doesn't return any values for the WHERE clause?

 

IE:

 

SELECT SUM(t1.salary) as t1total, SUM(t2.salary) as t2total
FROM table1 as t1
LEFT JOIN table2 as t2 ON (t2.id = t1.id)
WHERE t1.date BETWEEN '2008-10-01' AND '2008-10-20'
AND t1.username = 'test.account'
GROUP BY t1.date

 

Table 2 may not return any data if the user we are looking for hasn't submitted anything. For some reason mysql doesn't return any data at all when it can't SUM() an empty resultset?

 

Hope this makes sense, I tried using IFNULL(t2.salary,0) but it doesn't seem to work.

 

 

Thanks,

n3m.

[fenway]

For some reason mysql doesn't return any data at all when it can't SUM() an empty resultset?

As per the manual: "If the return set has no rows, SUM()  returns NULL."

 

Try:

 

SUM( IFNULL( t2.salary, 0 ) ) as t2total