mysql sum where sum result is Null

Question

I am using the below query which works fine if result of sum(otb.special_discount) is not null. but if the result is null the whole result of the sum query is returning null.

How I should correct this query that is for exampple sum(otb.total_charges) is 10000 and sum(otb.special_discount) is Null, I want the result to be 10000.

SELECT ott.test_name, (sum(otb.total_charges)- sum(otb.special_discount))as test_charges,
count(otb.id)count
from opd_test_bill otb,opd_test_type ott where otb.bill_type='p'
and otb.test_name=ott.id 
and date between '2015-04-26 16:00:59' and '2015-04-27 06:00:00' 
group by ott.test_name

Alex · Accepted Answer · 2015-04-28 18:50:00Z

1

Quick solution:

SELECT ott.test_name, (COALESCE(sum(otb.total_charges),0)- COALESCE(sum(otb.special_discount),0))as test_charges,
count(otb.id)count
from opd_test_bill otb,opd_test_type ott where otb.bill_type='p'
and otb.test_name=ott.id 
and date between '2015-04-26 16:00:59' and '2015-04-27 06:00:00' 
group by ott.test_name

https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

You can also use 'IFNULL':

https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

SELECT ott.test_name, (IFNULL(sum(otb.total_charges),0)- IFNULL(sum(otb.special_discount),0))as test_charges,
count(otb.id)count
from opd_test_bill otb,opd_test_type ott where otb.bill_type='p'
and otb.test_name=ott.id 
and date between '2015-04-26 16:00:59' and '2015-04-27 06:00:00' 
group by ott.test_name

edited Apr 28, 2015 at 18:50

answered Apr 28, 2015 at 18:43

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joshi Over a year ago

Great, It works just fine, BTW - how COALESCE works can you please explain a bit?

Alex Over a year ago

dev.mysql.com/doc/refman/5.0/en/…

Sagar Joon · Accepted Answer · 2015-04-28 18:49:13Z

0

Use COALESCE function :

     SELECT ott.test_name, 
     (sum(otb.total_charges)- COALESCE(sum(otb.special_discount),0))as test_charges,
     count(otb.id)count
     from opd_test_bill otb join opd_test_type ott 
     on otb.test_name=ott.id     
     where otb.bill_type='p'and date between '2015-04-26 16:00:59' and '2015-04-27 06:00:00' 
      group by ott.test_name

answered Apr 28, 2015 at 18:49

Sagar Joon

1,41715 silver badges23 bronze badges

Collectives™ on Stack Overflow

mysql sum where sum result is Null

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related