Page 1 of 1

Problems with SUM when no results found

Posted: Wed Jul 16, 2008 5:59 am
by someberry
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.

Code: Select all

...
...
WHERE   (
            SELECT  SUM(table2.cost)
            FROM    `table2`
            WHERE   table2.id = table.table2id
        ) <= table.total
If that subquery matches 1 or more rows then everything is gravy, but when it doesn't the whole thing goes to pot!

Re: Problems with SUM when no results found

Posted: Wed Jul 16, 2008 6:14 am
by VladSun

Re: Problems with SUM when no results found

Posted: Wed Jul 16, 2008 6:27 am
by someberry
Genius! Thanks very much. :)