query regarding sum function in sql

Aftab Hassan
Ranch Hand
Posts: 40
• 1
Hello,
I notice that on using a function like sum(col1) - sum(col2), if any one of the columns sums to 0, then the whole function doesn't work. Please see my screen capture of mysql below which would explain my question.
Below, HP and PH are columns and the sum of all rows in HP==0 which causes sum(HP)-sum(PH) or sum(PH)-sum(HP) not to work.
When the same thing is repeated in cases where neither of the columns sum to 0, this behavior is not seen.

Am I missing something? Is there a way to correct without using null checks etc?

Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
You can call coalesce(null,0) to convert that null to 0. The way coalesce works is that it returns the first non-null value in the list. For example:
coalesce(null, 0) returns 0
coalesce(5, 0) returns 5

K. Tsang
Bartender
Posts: 3583
16
It's pretty obvious if one of the sum(x) is "null" the result will be null. Therefore, either you disregard the nulls when doing the sum or if sum=null make it 0. I recommend the first approach.

Depending on what database you use, Oracle = NVL(), mssql = ISNULL() and mysql = IFNULL().

You can look into coalesce() too.

For MySQL anyway, if SUM() function ignores null values.

Aftab Hassan
Ranch Hand
Posts: 40
Jeanne Boyarsky wrote:You can call coalesce(null,0) to convert that null to 0. The way coalesce works is that it returns the first non-null value in the list. For example:
coalesce(null, 0) returns 0
coalesce(5, 0) returns 5

Thank you so much Jeanne, that worked just fine!

Aftab Hassan
Ranch Hand
Posts: 40
K. Tsang wrote:It's pretty obvious if one of the sum(x) is "null" the result will be null. Therefore, either you disregard the nulls when doing the sum or if sum=null make it 0. I recommend the first approach.

Depending on what database you use, Oracle = NVL(), mssql = ISNULL() and mysql = IFNULL().

You can look into coalesce() too.

For MySQL anyway, if SUM() function ignores null values.

Yes thanks Tsang, I went with coalesce().

Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
I'm glad you're working now. coalesce() works across different databases which is why I recommended it.