COALESCE not working as intended

Question

I have the following query:

SELECT SUM(trns_master.trns_advance) FROM trns_master
WHERE trns_company = 1 AND 
(trns_rentTrnsptr = 12 OR  trns_othersTrnsptr = 12)

The above query return the amount: 10000 suppose

Then I have a second query:

SELECT SUM(txn_credit) FROM txn_master
WHERE txn_fromLedId = 1 AND txn_fromLedId = 12

Because there are no matching rows, a null value is returned.

Now what I want is the sum of the values returned from the above two queries

For which I use the COALESCE function as follows:

SELECT COALESCE(SUM(trns_master.trns_advance), 0) + COALESCE(SUM(txn_master.txn_credit), 0)
FROM trns_master, txn_master WHERE  
trns_master.trns_company = 1 AND 
txn_master.txn_fromLedId = 1 AND  
txn_master.txn_toLedId = 12 AND 
(trns_rentTrnsptr = 12 OR  trns_othersTrnsptr = 12)

So from what I can understand from the COALESCE definition which says that the function returns first NON-NULL value,

COALESCE(SUM(trns_master.trns_advance), 0) should return 10000 and COALESCE(SUM(txn_master.txn_credit), 0) should return 0 because SUM(txn_master.txn_credit) is null.

But the end-result is 0.

Where is my mistake?

Stuart Thomson Stuart Thomson · Accepted Answer · 2014-08-05T17:58:30

The COALESCE part is actually fine I believe, I don't have access to SQL at the moment to check. If the output is not as expected, replace the functions with the expected output value.

SELECT COALESCE(10000,0) + COALESCE(NULL,0)

This should return 10000 as the value. The following therefore should give you what you're expecting:

SELECT COALESCE(
    (SELECT SUM(trns_master.trns_advance)
    FROM trns_master
    WHERE trns_company = 1 AND 
    (trns_rentTrnsptr = 12 OR  trns_othersTrnsptr = 12))
,0)
+
COALESCE(
    (SELECT SUM(txn_credit) FROM txn_master
    WHERE txn_fromLedId = 1 AND txn_fromLedId = 12)
,0)

COALESCE not working as intended

1 Answers