• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query regarding sum function in sql

 
Aftab Hassan
Ranch Hand
Posts: 40
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

In your example:
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

In your example:

Thank you so much Jeanne, that worked just fine!
 
Aftab Hassan
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm glad you're working now. coalesce() works across different databases which is why I recommended it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic