Search code examples
mysqlcoalesce

How to resolve null values correctly during a COALESCE call

I have a pretty large mysql call which I won't bother showing it all as it's not really important. The part that IS working fine before requiring modification looked like this:

COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq, d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',

This would return all the apFunReq values in multiple tables, regardless if null or filled. Worked great, no problems.

Now it's been decided we need another tables data to join the process, however the required data does not sit all in one column on this new table, instead it needs to be added up collectively over 5 columns. So I have implemented this:

COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq, (IFNULL(d10.reqFunding_01, 0) + IFNULL(d10.reqFunding_02, 0) + IFNULL(d10.reqFunding_03, 0) + IFNULL(d10.reqFunding_04, 0) + IFNULL(d10.reqFunding_05, 0)), d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',

Now this does actually work for the new table rows in the result BUT it breaks a significant portion of what was previously returning ok. Now much of the other tables values are just returning as 0.

Clearly I need to return null values to be 0 for data within d10 for the addition to work, but I want null values from the other tables to remain null and I want the data for all tables to obviously work as desired.

I'm not sure what I am doing wrong in order to get the desired result. Can someone help me on this please?

C

Solution

  • The simple way to solve this is to use NULLIF after you add up all the IFNULLed values like so: 

     COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq, 
   NULLIF(IFNULL(d10.reqFunding_01, 0) + IFNULL(d10.reqFunding_02, 0) + IFNULL(d10.reqFunding_03, 0) + IFNULL(d10.reqFunding_04, 0) + IFNULL(d10.reqFunding_05, 0), 0), 
   d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',

    Obviously, this doesnt work if 0 is a valid answer though.