0

I have a SQL statement something like this

ISNULL(revenue - expense, 0)

I'm trying to find if one or both of my columns (revenue AND/OR expense) are blank. If either or both are blank then return 0. I keep getting #Error messages as a result though.

Jane Alice
  • 91
  • 9
  • 2
    Possible duplicate of [ISNULL(value, 0) in WHERE clause MYSQL](https://stackoverflow.com/questions/42932426/isnullvalue-0-in-where-clause-mysql) – GSerg Oct 22 '18 at 15:02
  • Please see: [Why should I accept an answer when my query is resolved](https://meta.stackexchange.com/a/5235) – Madhur Bhaiya Oct 24 '18 at 11:17

4 Answers4

1

Your statement suggests:

COALESCE(revenue - expense, 0)

However, logic dictates that you might really want:

COALESCE(revenue, 0) - COALESCE(expense, 0)

That is, treat each one individually as 0 if the value is NULL.

Note that number values are not really "blank" (which implies an empty string). They are NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You could use COALESCE():

SELECT COALESCE(revenue - expense, 0)
FROM tab_name;
Blue
  • 22,608
  • 7
  • 62
  • 92
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

Besides COALESCE, you can also use IfNull() function in MySQL:

SELECT IFNULL(revenue - expense, 0) 
FROM your_table_name
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
0

Use coalesce function below way

     Select  Coalesce(revnue,0)-coalesce(expense,0)  
     from t
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63