Problems with SUM when no results found
Posted: Wed Jul 16, 2008 5:59 am
I'm tearing my hair out trying to get SUM(column) return a double instead of NULL when the WHERE matches no rows. I've scoured the MySQL manual trying things like CAST, CONVERT to DECIMAL, ABS, etc to no avail.
If that subquery matches 1 or more rows then everything is gravy, but when it doesn't the whole thing goes to pot!
Code: Select all
...
...
WHERE (
SELECT SUM(table2.cost)
FROM `table2`
WHERE table2.id = table.table2id
) <= table.total