CODEWITHSUNDEEP
mysqlsql
helloworld
helloworld

Reputation: 527

Subtraction results in Null when empty SUM in MySQL

I'm running the following query in MySQL:

SELECT (SUM(total) / 100) - 

(SELECT (SUM(other_table.total) / 100) FROM other_table WHERE 1) ) AS total

FROM orders WHERE 1

It works as expected if there is at least one row in other_table.

If there are no rows in other_table the result is NULL.

I know this happens because (SUM(other_table.total) gives NULL when there is nothing to SUM.

My question is: How could I update the query to account for the possibility of having an empty other_table

Upvotes: 0

Views: 265

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Use COALESCE :

SELECT (SUM(total) / 100) - 

COALESCE( (SELECT (SUM(other_table.total) / 100) FROM other_table WHERE 1) ,0) AS total

FROM orders WHERE 1

Upvotes: 3

Related Questions