CODEWITHSUNDEEP
mysqlsql
DatsunBing
DatsunBing

Reputation: 9076

MySQL: Adding columns where one has NULL values

Here is my query:

SELECT (contract_value + amendment_value) AS total_value
FROM contracts
ORDER BY total_value DESC

When I run it, the result for each row is NULL. Why is that so?

Each row has either a contract_value or an amendment_value, but not both. Where there is no value, it is NULL. Could that be the problem? (I could not find anything in the documentation to suggest it). If so, how do I get around it?

Upvotes: 1

Views: 35

Answers (2)

Fahmi
Fahmi

Reputation: 37473

you can use coalesce() - all are showing null because any of your two value is null - with null value any calculation will return as null, so in this case, you've to replace null with 0

SELECT coalesce(contract_value,0) + coalesce(amendment_value,0) AS total_value
FROM contracts
ORDER BY total_value DESC

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Use COALESCE:

SELECT
    COALESCE(contract_value, amendment_value) AS total_value
FROM contracts
ORDER BY total_value DESC;

Upvotes: 3

Related Questions