Problems with SUM when no results found

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Problems with SUM when no results found

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with SUM when no results found

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Re: Problems with SUM when no results found

Post by someberry »

Genius! Thanks very much. :)
Post Reply